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

Example:

CREATE TABLE NumberTable (
    Number INTEGER NOT NULL
);

The below query would give an empty NULL row when no data is present.

SELECT MAX(Number) AS MaxNumber
  FROM NumberTable;

The NULL row result looks like this:

	MaxNumber
1	NULL

To detect if the query returns data or not I had to do this:

SELECT 1
  FROM (
           SELECT MAX(Number) AS MaxNumber
             FROM NumberTable
       )
 WHERE MaxNumber IS NOT NULL;

Returns 1 if a max number exists and 0 if empty table. Is this how we generally tackle the empty row produced by the aggregate function or is there a more generalized way?

Usecase in SQLite fiddle.

Fiddle is useful for demonstration, but we ask that posts includes complete examples

Usecase example (See comments in the example):-

CREATE TABLE details
  (
     tourcompletiondatetime VARCHAR NOT NULL,
     tourid                 VARCHAR NOT NULL,
     PRIMARY KEY ( tourid )
  );

-- Inserting new data only if new TourCompletionDateTime is greater than all old TourCompletionDateTime
-- Here the new date is '2022-07-26T09:36:00.730589Z'
INSERT INTO details
            (tourcompletiondatetime,
             tourid)
SELECT '2022-07-26T09:36:00.730589Z',
       'tour5416'
WHERE  EXISTS (SELECT 1
               FROM   (SELECT Max(tourcompletiondatetime) AS
                              MaxTourCompletionDateTime
                       FROM   details)
               WHERE  maxtourcompletiondatetime < '2022-07-26T09:36:00.730589Z'
                       OR maxtourcompletiondatetime IS NULL);

Query finished in 0.011 second(s). Rows affected: 1

SELECT * FROM details;

TourCompletionDateTime	TourID
2022-07-26T09:36:00.730589Z	tour5416

-- This should add data
-- Insert data with future DateTime than the max DateTime in the database.
INSERT INTO details
            (tourcompletiondatetime,
             tourid)
SELECT '2022-07-26T10:36:00.730589Z',
       'tour5417'
WHERE  EXISTS (SELECT 1
               FROM   (SELECT Max(tourcompletiondatetime) AS
                              MaxTourCompletionDateTime
                       FROM   details)
               WHERE  maxtourcompletiondatetime < '2022-07-26T10:36:00.730589Z'
                       OR maxtourcompletiondatetime IS NULL);

Query finished in 0.018 second(s). Rows affected: 1

SELECT * FROM details;

TourCompletionDateTime	TourID
2022-07-26T09:36:00.730589Z	tour5416
2022-07-26T10:36:00.730589Z	tour5417

-- This should not add data
-- Insert data with past DateTime than the max DateTime in the database.
INSERT INTO details
            (tourcompletiondatetime,
             tourid)
SELECT '2022-07-26T08:36:00.730589Z',
       'tour5418'
WHERE  EXISTS (SELECT 1
               FROM   (SELECT Max(tourcompletiondatetime) AS
                              MaxTourCompletionDateTime
                       FROM   details)
               WHERE  maxtourcompletiondatetime < '2022-07-26T08:36:00.730589Z'
                       OR maxtourcompletiondatetime IS NULL);

Query finished in 0.000 second(s). Rows affected: 0

SELECT * FROM   details;

TourCompletionDateTime	TourID
2022-07-26T09:36:00.730589Z	tour5416
2022-07-26T10:36:00.730589Z	tour5417

This question is not relevant to this question. So, don't suggest that. The previous question's max aggregate function was not getting used in WHERE condition but this question's use case makes use of max aggregate in usecase.

CodePudding user response：

Use HAVING <predicate> to filter out rows after aggregating.

The modified query:

SELECT MAX(Number) AS MaxNumber
FROM NumberTable
HAVING MAX(Number) is not null

Will return the maximum number. In case the maximum number evaluates to null, it will return zero rows instead.

See example at db<>fiddle.