Search code examples
sqlpostgresqlgroup-byhavinghaving-clause

Postgres: How to make NULL friendly MIN filtering in Having clause?

audit_table might contain repeated userId.

We have following SQL query

SELECT userId FROM audit_table
GROUP BY userId 
HAVING MIN(updatedDate) > ?;

updatedDate field might be null and for case when all rows in audit_table for concrete userId contains only nulls I don't see these rows but I want. Is there way to acheve it ?

Solution

  • One method is an explicit comparison:

    SELECT userId FROM audit_table
GROUP BY userId 
HAVING MIN(updatedDate) > ? OR MIN(updatedDate) IS NULL;

    Another method is to count the updatedDates that are less than ? or NULL:

    HAVING COUNT(*) FILTER (WHERE updatedDate <= ?) = 0;