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.

About the author

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

4 comments

Leave a Reply to tuyennguyencanada Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Starting in SQL 2005, you could simply use:
    NULLIF(MAX(COALESCE(EndDate, ’12/31/2099′)),’12/31/2099′) AS Date

  • 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.

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me