Odd behaviour of UNION
(1) By pythonizer on 2021-11-09 12:46:27 [link] [source]
Consider the following simple scenario:
CREATE TABLE A (
c1 INTEGER PRIMARY KEY,
c2 INTEGER
);
INSERT INTO A VALUES (1, 100), (2, 90), (3, 100);
I want to get the highest and second highest values in c2. I know this is very simple, but I was explaining this to someone else and they asked how they could use UNION for this.
I gave them the following example:
SELECT max(c2) FROM A
UNION
SELECT DISTINCT c2 FROM A ORDER BY c2 DESC LIMIT 1 OFFSET 1;
However, this just gives me:
c2
----------
90
I would expect it to give me 90 and 100.
Again, I understand that there are much easier ways to do this, but I just would like to understand why this does not work.
Thanks!
(2) By Richard Hipp (drh) on 2021-11-09 13:03:11 in reply to 1 [source]
The ORDER BY, LIMIT, and OFFSET apply to the UNION, not to the second SELECT. You want this, I think:
SELECT max(c2) FROM A UNION SELECT * FROM (SELECT DISTINCT c2 FROM A ORDER BY c2 DESC LIMIT 1 OFFSET 1);
(3) By pythonizer on 2021-11-09 14:31:49 in reply to 2 [link] [source]
Ah! Right.
You can clearly see here that the ORDER BY
and LIMIT
clauses are applied after the UNION (compound-operator).
Thanks!