Re: ORDER BY handling mixed integer and varchar values

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>