Consider the following query:
SELECT (SELECT MIN(col1) FROM table1) = 7;
Assuming col1
is non-NULLable, this will yield either true or false – or possibly NULL when table1
is empty;
But now suppose I have:
SELECT ( SELECT FIRST_VALUE (col2) OVER ( ORDER BY col1 ) AS col2_for_first_col1 FROM table1 ) = 7;
(and assume col2
is also non-NULLable for simplicity.)
If there is a unique col2
value for the lowest col1
value, or the table is empty, then this works just like before. But if there are multiple col2
values for the lowest col1
, I’m going to get a query runtime error.
My question: What is a short, elegant way to get NULL
from this last query also in the case of multiple inner-query results? I could of course duplicate it and check the count, but I would rather avoid that.
Important caveat: I’m using MonetDB, and it doesn’t seem to support ORDER BY ... LIMIT 1
on inner queries.
Advertisement
Answer
Without the MonetDB limitation, you would seem to want:
SELECT (SELECT col2 FROM table1 ORDER BY col1 LIMIT 1 ) = 7;
with the limitation, you can use window functions differently:
SELECT (SELECT col2 FROM (SELECT col2, ROW_NUMBER() OVER (ORDER BY col1) as seqnum FROM table1 ) t WHERE seqnum = 1 ) = 7;