Need to overcome UNION / ORDER BY restriction
От | Timo |
---|---|
Тема | Need to overcome UNION / ORDER BY restriction |
Дата | |
Msg-id | bl9fh3$1krk$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: Need to overcome UNION / ORDER BY restriction
Re: Need to overcome UNION / ORDER BY restriction |
Список | pgsql-sql |
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);
В списке pgsql-sql по дате отправления: