How can I include null values in a MIN or MAX?

Addressing the nuances

While infusing replacement value for NULL, place weight on maintaining data type consistency with the column. For instance, if dealing with NULLs in numeric columns, replace them with appropriate numeric extremes:

SELECT MIN(COALESCE(startDate, '19000101')) AS EarliestStartDate FROM yourTable; 
-- For datetime columns, '19000101' serves as an early representative date


For cases where NULL signifies ongoing timespans, consider GETDATE() to provide an up-to-date maximum:

SELECT MAX(COALESCE(endDate, GETDATE())) AS LatestEndDate FROM yourTable;
-- Treats NULL as "today", making it as up-to-date as possible


Juggling with group scenarios

With GROUP BY involved in your calculations alongside MIN and MAX, you might run into NULL values causing improper skewing. Group your results on RecordID or a meaningful key to ensure that your aggregates treat NULLs consistently:

SELECT RecordID, MIN(COALESCE(startDate, '19000101')), MAX(COALESCE(endDate, GETDATE()))
FROM yourTable
GROUP BY RecordID; -- One record, one group! NULLs said they will also participate.


This measure caters towards the integrity of your grouped calculations, making null values a team player in the aggregation.

Paddling through alternatives

Count on NULLIF

In circumstances where you wish to exclude particular values from your MIN or MAX calculations and convert them into NULL, NULLIF steps in. It hands out a NULL if the two expressions put up an equal fight:

SELECT MIN(NULLIF(column, 'condition value')) FROM table;
-- Not all values deserve to be MIN or MAX. NULLIF discounts the 'condition value'


The counting game

When you're curious about the presence of NULL values during your aggregations, play around with COUNT(column) and COUNT(*). COUNT(column) counts only the non-NULL values:

SELECT
    COUNT(endDate), -- This one is shy, ignore NULLs
    COUNT(*) -- All for one! counts regardless of NULLs
FROM yourTable;


Calling up CASE statements

Bring out the CASE statement to include NULLs into your calculations when working with more complex conditions:

SELECT 
    MIN(CASE WHEN column IS NULL THEN 'default value' ELSE column END) 
FROM table; -- CASE statements to the rescue with NULL handling!
