Ordering by a complex field

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

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