Select NULL AS Max or Min in SQL Query

By default the functions MAX and MIN do not count NULL in their evaluation of your data.  If we have a column containing only dates for instance and there is a NULL date, MAX and MIN will both ignore that value.  For instance, take the following data set as an example.

WorkID    StoreID       EndDate
———– ———– ———————–
1              50              NULL
2              10             1900-01-01 00:00:00.000
3              20             1925-01-01 00:00:00.000
4              30             1950-01-01 00:00:00.000
5              40             1975-01-01 00:00:00.000
6              19             2010-01-01 00:00:00.000
7              34             2010-01-01 00:00:00.000
8              50             2010-01-01 00:00:00.000

If we are to SELECT MAX(EndDate) From WorkSchedule we would get 1/1/2010 as the result, even when grouping by StoreID.  Store 50 would bring back an EndDate of 1/1/2010.  The business rules in this case say that we want to pull back the maximum end date for each store.  However, if there is a NULL EndDate then the store is still being listed as active and we need to bring back NULL instead of any other dates listed.  Luckily we can reqrite the query to trick MAX into picking the NULL value.  Combine that with a case statement and we are good to go.

We will use COALESCE to replace any NULL EndDate with a date that is in the future that will not be coming up in our data anywhere, December 31, 2099 seems like a reasonable date for this.  Next we take the MAX of the dates, which if NULL will evaluate as 12/31/2099 and be greater than any other date in our table.  Wrap that in a CASE statement to replace the date 12/31/2099 back to NULL and group our data by StoreID.

SELECT
StoreID,
CASE WHEN MAX(COALESCE(EndDate, ’12/31/2099′)) = ’12/31/2099′ THEN NULL ELSE MAX(EndDate) END AS Date
FROM WorkSchedule
GROUP BY StoreID

Not the most eligant way to do things, but it defeinitely gets the job done.  This can work with minimums as well, just select a very low date like 1/1/1900 if you want NULL to be the minimum.  The same method can be used on regular integer fields as well substituting a number for NULL.

If you want to be able to USE AVG and other aggregate functions with NULL values this is a great way to accomplish that.  You may want to average NULL values as zero so using SELECT AVG(COALESCE(column, 0)) FROM Table will accomplish that.  Then any NULL will be treated as a zero.

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

4 Responses

  1. McOz says:

    Starting in SQL 2005, you could simply use:

    NULLIF(MAX(COALESCE(EndDate, ’12/31/2099′)),’12/31/2099′) AS Date

  2. Hilda says:

    Great article, this is exactly what I was looking for.

  3. Amy-Etymotic Research says:

    This is related to an issue I am currently having. I have a table with contact info and a table with their orders. ID # on the contacts is linked to the orders customer # as a one to many relationship. (One contact can have many orders).

    I built a query with the contact and orders tables, grouped by the linked customer number in orders and made a max order date field.

    When I changed my form to display from this query, I lost all my opportunity accounts–those with no max order date. I also lost the ability to add new records.

    How can I make the query show ALL records even those with no orders and therefore no max order date?

    Thank you.

  4. Thank you, this is what I was looking for.

Leave a Reply