ORDER BY handling mixed integer and varchar values

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message PFC 2005-05-16 19:12:28 Re: ORDER BY handling mixed integer and varchar values
Previous Message PFC 2005-05-16 18:26:48 Re: choosing index to use