Skip to content
Advertisement

How to express “either the single resulting record or NULL”, without an inner-query LIMIT?

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;
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement