Message-ID: <4288F1FE.5050002@onesystem.com>
Date: Mon, 16 May 2005 13:18:22 -0600
From: Edmund Bacon <ebacon@onesystem.com>
User-Agent: Mozilla Thunderbird 0.9 (X11/20041103)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: David B <davidb999@gmail.com>
Subject: Re: ORDER BY handling mixed integer and varchar values
References: <c76ea21c050516114742818cfa@mail.gmail.com>
In-Reply-To: <c76ea21c050516114742818cfa@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

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>