Thread: ORDER BY handling mixed integer and varchar values

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.

> 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...

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@onesystem.com>

> 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.
-- 

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