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!