Re: Ordering by a complex field

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