Thread: Ordering by a complex field

I have a one varchar field.

I'd like to order so that records where field='2' come first, then '1', then '9', then anything but '0', then '0'.  Is there anyway to do this in a standard order by clause (that is, without writing a new SQL function)?

On Jul 18, 2007, at 20:12 , Robert James wrote:

> I'd like to order so that records where field='2' come first, then
> '1', then
> '9', then anything but '0', then '0'.  Is there anyway to do this in a
> standard order by clause (that is, without writing a new SQL
> function)?

# create table whatever (a text primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"whatever_pkey" for table "whatever"
CREATE TABLE
# insert into whatever (a) select a::text from generate_series(0,20)
as g(a);
INSERT 0 21
# SELECT a
FROM whatever
ORDER BY a = '2' DESC
     , a = '1' DESC
     , a = '9' DESC
     , a <> '0' DESC;
a
----
2
1
9
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
3
4
0
(21 rows)

Michael Glaesemann
grzm seespotcode net

In article <e09785e00707181812l628f1634j163a4190111dc73a@mail.gmail.com>,
Robert James <srobertjames@gmail.com> wrote:

% I'd like to order so that records where field='2' come first, then '1', then
% '9', then anything but '0', then '0'.  Is there anyway to do this in a
% standard order by clause (that is, without writing a new SQL function)?

You can use a case statement in the order by clause

 order by case when field = '0' then 4
               when field = '1' then 1
               when field = '2' then 0
               when field = '9' then 2
               else 3
          end
--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com