From: Timo <siroco@suomi24.fi>
To: pgsql-sql@postgresql.org
Subject: Need to overcome UNION / ORDER BY restriction
Date: Mon, 29 Sep 2003 17:27:47 +0300
Message-ID: <bl9fh3$1krk$1@news.hub.org> (raw)
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);
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: siroco@suomi24.fi
Subject: Re: Need to overcome UNION / ORDER BY restriction
In-Reply-To: <bl9fh3$1krk$1@news.hub.org>
* 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