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.

В списке pgsql-sql по дате отправления:

Предыдущее
От: PFC
Дата:
Сообщение: Re: choosing index to use
Следующее
От: PFC
Дата:
Сообщение: Re: ORDER BY handling mixed integer and varchar values