Thread: Need to overcome UNION / ORDER BY restriction
I have a table: id | priority | seniority -----+----------+-----------902 | 2 | 271902 | 1 | 271902 | 3 | 271924 | 2| 581924 | 1 | 581924 | 3 | 581935 | 1 | 276935 | 2 | 276972 | 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);
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 | 271935 | 1 | 276924 | 1 | 581902 | 2| 271902 | 3 | 271972 | 2 | 275935 | 2 | 276924 | 2 | 581924 | 3 | 581 (9 rows)
"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