Generalized way to remove empty row given by aggregate functions in SQLite-CodePudding

Please look at this short example:-

The below query would give an empty NULL row when no data is present in the Table or when WHERE fails.

SELECT MAX(Number) AS Number
  FROM Table
 WHERE Number > 10;

The NULL row result looks like this:-

	Number
1	NULL

So to detect if the query gives actual data or not I had to do this:-

SELECT 1
  FROM (
           SELECT MAX(Number) AS Number
             FROM Table
            WHERE Number > 10
       )
 WHERE Number IS NOT NULL;

Now, this will give me 1 if a max number exists and 0 if (max it doesn't exist or empty table or WHERE Number < 10).

So, is this how we generally tackle the empty row produced by the aggregate function, or is there a more generalized way to do that.

An example SQLite fiddle showcasing the use case.

Thank you.

CodePudding user response：

You are overcomplicating things with the use of the aggregate function MAX().

This part of your code:

EXISTS (
        SELECT 1
        FROM (
          SELECT MAX(TourCompletionDateTime) AS TourCompletionDateTime
          FROM Details
          WHERE TourCompletionDateTime < '2022-07-26T09:36:00.730589Z'
        )
        WHERE TourCompletionDateTime IS NOT NULL
      )

is equivalent to just:

EXISTS (
        SELECT 1
        FROM Details
        WHERE TourCompletionDateTime < '2022-07-26T09:36:00.730589Z'
      )

See the demo.