pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed to overcome UNION / ORDER BY restriction
3+ messages / 3 participants
[nested] [flat]
* Need to overcome UNION / ORDER BY restriction
@ 2003-09-29 14:27 Timo <siroco@suomi24.fi>
2003-09-29 17:15 ` Re: Need to overcome UNION / ORDER BY restriction Bruno Wolff III <bruno@wolff.to>
2003-09-29 18:06 ` Re: Need to overcome UNION / ORDER BY restriction Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Timo @ 2003-09-29 14:27 UTC (permalink / raw)
To: pgsql-sql
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);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need to overcome UNION / ORDER BY restriction
2003-09-29 14:27 Need to overcome UNION / ORDER BY restriction Timo <siroco@suomi24.fi>
@ 2003-09-29 17:15 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2003-09-29 17:15 UTC (permalink / raw)
To: Timo <siroco@suomi24.fi>; +Cc: pgsql-sql
On Mon, Sep 29, 2003 at 17:27:47 +0300,
Timo <siroco@suomi24.fi> wrote:
>
> 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
You can do something like:
bruno=> select * from foo order by priority <> 1, seniority, priority;
id | priority | seniority
-----+----------+-----------
902 | 1 | 271
935 | 1 | 276
924 | 1 | 581
902 | 2 | 271
902 | 3 | 271
972 | 2 | 275
935 | 2 | 276
924 | 2 | 581
924 | 3 | 581
(9 rows)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need to overcome UNION / ORDER BY restriction
2003-09-29 14:27 Need to overcome UNION / ORDER BY restriction Timo <siroco@suomi24.fi>
@ 2003-09-29 18:06 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-09-29 18:06 UTC (permalink / raw)
To: Timo <siroco@suomi24.fi>; +Cc: pgsql-sql
"Timo" <siroco@suomi24.fi> writes:
> 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..)
You'd need to parenthesize:
(SELECT * from foo where priority = 1 order by seniority)
UNION ALL
(select * from foo where priority > 1 order by seniority, priority)
Otherwise the ORDER BY is considered to apply to the whole UNION result
(it's effectively got lower binding priority than the UNION). Note also
that you *must* use UNION ALL, else UNION will attempt to eliminate
duplicates, and mess up the sort order while at it.
See also Bruno's solution nearby. Not sure which of these approaches
would be faster; try both.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-09-29 18:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-29 14:27 Need to overcome UNION / ORDER BY restriction Timo <siroco@suomi24.fi>
2003-09-29 17:15 ` Bruno Wolff III <bruno@wolff.to>
2003-09-29 18:06 ` Tom Lane <tgl@sss.pgh.pa.us>
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