Lists: | pgsql-sql |
---|
From: | David B <davidb999(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | ORDER BY handling mixed integer and varchar values |
Date: | 2005-05-16 18:47:59 |
Message-ID: | c76ea21c050516114742818cfa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Hi All,
I have a tabe
Product_desc varchar(100)
Product_price integer
Product_cat varchar(100)
The problem…
We have categories such as:
Electronics
White Goods
1
2
5
15
25
etc
I have a query
Select product_desc, product_price, product_cat
Order by product_cat, product_price
And of course I get stuff ordered as I want it.
BUT… with many product categories being numeric based they come out in
wrong order '10 comes before 2" etc.
So I tried
Select product_desc, product_price, product_cat
Order by cast(product_cat as integer), product_price
And that worked for the numberic based categories.
I don't know of a query will be across alpha or numeric categories.
Is there any elegent query you folks can think of that combines the
two so I can one query that has alpha sorting on alpha categories and
numeric sorting on numeric values that are in the same column??
Tia.
From: | PFC <lists(at)boutiquenumerique(dot)com> |
---|---|
To: | "David B" <davidb999(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ORDER BY handling mixed integer and varchar values |
Date: | 2005-05-16 19:12:28 |
Message-ID: | op.sqvua2ogth1vuj@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
> Is there any elegent query you folks can think of that combines the
> two so I can one query that has alpha sorting on alpha categories and
> numeric sorting on numeric values that are in the same column??
solution 1 (fast)
make a separate column which contains the integer value (updated via a
trigger) or NULL if it's a textual value, then sort on it
solution 2
order by the string padded to a fixed length by adding spaces to the left :
(here an underscore is a space):
____1
___10
_ABCD
I think there's a LPAD function (look in the docs) to do that... you can
use only the first N (like 10) chars of the string...
From: | Edmund Bacon <ebacon(at)onesystem(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | David B <davidb999(at)gmail(dot)com> |
Subject: | Re: ORDER BY handling mixed integer and varchar values |
Date: | 2005-05-16 19:18:22 |
Message-ID: | 4288F1FE.5050002@onesystem.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Well the following seems to work, althoug I do not believe it is
guarenteed to:
(select * from table where column ~'\\d+' order by cast(colum as integer))
union all
(select * from table where column !~ '\\d+' order by column);
This could be quite slow if table is large
Alternatively:
select * from table order by case when column ~ '\\d+' cast(column as
integer) else null end, column)
This will sort all 'integer' values of column ahead of non-integer
values. If you want non-integer then integer use
... else -1 end, ...
(assuming all integer values of column are >= 0)
David B wrote:
>Hi All,
>I have a tabe
>
>Product_desc varchar(100)
>Product_price integer
>Product_cat varchar(100)
>
>The problem…
>
>We have categories such as:
>
>Electronics
>White Goods
>1
>2
>5
>15
>25
>etc
>
>I have a query
>
>Select product_desc, product_price, product_cat
>Order by product_cat, product_price
>
>And of course I get stuff ordered as I want it.
>BUT… with many product categories being numeric based they come out in
>wrong order '10 comes before 2" etc.
>
>So I tried
>Select product_desc, product_price, product_cat
>Order by cast(product_cat as integer), product_price
>
>And that worked for the numberic based categories.
>
>I don't know of a query will be across alpha or numeric categories.
>
>Is there any elegent query you folks can think of that combines the
>two so I can one query that has alpha sorting on alpha categories and
>numeric sorting on numeric values that are in the same column??
>
>Tia.
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
--
Edmund Bacon <ebacon(at)onesystem(dot)com>
From: | Rod Taylor <pg(at)rbt(dot)ca> |
---|---|
To: | David B <davidb999(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ORDER BY handling mixed integer and varchar values |
Date: | 2005-05-16 19:19:24 |
Message-ID: | 1116271164.696.80.camel@home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
> Is there any elegent query you folks can think of that combines the
> two so I can one query that has alpha sorting on alpha categories and
> numeric sorting on numeric values that are in the same column??
select * from r order by (case when col ~ '^[0-9]+$' THEN lpad(col, 10,
'0') else col end) ;
Left pad the numbers with 0's, but don't touch the text strings. Sort
based on that.
--
From: | Ragnar Hafstað <gnari(at)simnet(dot)is> |
---|---|
To: | David B <davidb999(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ORDER BY handling mixed integer and varchar values |
Date: | 2005-05-16 19:32:40 |
Message-ID: | 1116271960.8157.4.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
On Mon, 2005-05-16 at 11:47 -0700, David B wrote:
(sorting text columns numerically)
> And of course I get stuff ordered as I want it.
> BUT… with many product categories being numeric based they come out in
> wrong order '10 comes before 2" etc.
>
> So I tried
> Select product_desc, product_price, product_cat
> Order by cast(product_cat as integer), product_price
>
> And that worked for the numberic based categories.
>
> I don't know of a query will be across alpha or numeric categories.
> Is there any elegent query you folks can think of that combines the
> two so I can one query that has alpha sorting on alpha categories and
> numeric sorting on numeric values that are in the same column??
select product_desc, product_price, product_cat
order by cast(product_cat as integer), product_cat, product_price
gnari