Re: Need to overcome UNION / ORDER BY restriction

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);

On Mon, Sep 29, 2003 at 17:27:47 +0300,
Timo <siroco(at)suomi24(dot)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)

"Timo" <siroco(at)suomi24(dot)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