From: "Timo" <siroco@suomi24.fi>
Subject: Need to overcome UNION / ORDER BY restriction
Date: Mon, 29 Sep 2003 17:27:47 +0300
Organization: Hub.Org Networking Services
Lines: 68
Message-ID: <bl9fh3$1krk$1@news.hub.org>
To: pgsql-sql@postgresql.org


I have a table:

 id  | priority | seniority
-----+----------+-----------
 902 |        2 |       271
 902 |        1 |       271
 902 |        3 |       271
 924 |        2 |       581
 924 |        1 |       581
 924 |        3 |       581
 935 |        1 |       276
 935 |        2 |       276
 972 |        2 |       275
(9 rows)

I'd need to get a result set where rows are sorted according to these rules:

1. first all rows with priority = 1 sorted according to seniority
2. then the rest of the rows sorted by seniority, priority

Something like this:

SELECT * from foo where priority = 1 order by seniority
    union select * from foo where priority > 1 order by seniority, priority

but this gives parse error because of the restrictions with ORDER BY and
UNION (I suppose..)


select * from foo order by case when priority = 1 then priority else
seniority end;

goes fine, but it's not quite enough and when I try

select * from foo order by case when priority = 1 then priority else
seniority, priority end;

it's parse error at or near ",".

Any suggestions?

Thanks in advance,
Timo

---------------

CREATE TABLE foo (
    id integer,
    priority integer,
    seniority integer
);


INSERT INTO foo VALUES (902, 2, 271);
INSERT INTO foo VALUES (902, 1, 271);
INSERT INTO foo VALUES (902, 3, 271);
INSERT INTO foo VALUES (924, 2, 581);
INSERT INTO foo VALUES (924, 1, 581);
INSERT INTO foo VALUES (924, 3, 581);
INSERT INTO foo VALUES (935, 1, 276);
INSERT INTO foo VALUES (935, 2, 276);
INSERT INTO foo VALUES (972, 2, 275);