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

by

It’s a bit ugly but because the NULLs have a special meaning to you, this is the cleanest way I can think to do it:

SELECT recordid, MIN(startdate),
   CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0
        THEN MAX(enddate)
   END
FROM tmp GROUP BY recordid

That is, if any row has a NULL, we want to force that to be the answer. Only if no rows contain a NULL should we return the MIN (or MAX).

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. How do I obtain a Query Execution Plan in SQL Server?
  4. How do I escape a single quote in SQL Server?
  5. Simple way to transpose columns and rows in SQL?
  6. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  7. Only inserting a row if it’s not already there
  8. Unpivot with column name
  9. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  10. SQL JOIN vs IN performance?
  11. Most efficient way in SQL Server to get date from date+time?
  12. Convert varchar into datetime in SQL Server
  13. TSQL Pivot without aggregate function
  14. How to generate a range of dates in SQL Server
  15. SQL RANK() versus ROW_NUMBER()
  16. Generate SQL Create Scripts for existing tables with Query
  17. Check if a row exists, otherwise insert
  18. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  19. How do I perform a GROUP BY on an aliased column in SQL Server?
  20. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  21. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  22. Alter user defined type in SQL Server
  23. Store multiple bit values in a single table column
  24. SQL Server equivalent to MySQL enum data type?
  25. How to correctly insert newline in nvarchar [duplicate]
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. SQL – Call Stored Procedure for each record
  29. COALESCE Function in TSQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment