(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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
