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.

Select NULL AS Max or Min in SQL Query

Comments

More from this blog

Azure Data Studio is dead...now what?

Working with Fabric shortcuts in PySpark

Gathering useful notebook and environment details at runtime

Using sempy to get SQL query CU cost from the Fabric Capacity Metrics app

List Parameters for a Python Function

Command Palette

Comments

More from this blog