From: Edmund Bacon <ebacon@onesystem.com>
To: pgsql-sql@postgresql.org
Cc: David B <davidb999@gmail.com>
Subject: Re: ORDER BY handling mixed integer and varchar values
Date: Mon, 16 May 2005 13:18:22 -0600
Message-ID: <4288F1FE.5050002@onesystem.com> (raw)
In-Reply-To: <c76ea21c050516114742818cfa@mail.gmail.com>
References: <c76ea21c050516114742818cfa@mail.gmail.com>
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ebacon@onesystem.com, davidb999@gmail.com
Subject: Re: ORDER BY handling mixed integer and varchar values
In-Reply-To: <4288F1FE.5050002@onesystem.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox