Selecting minimal dates, or nulls in SQL

sql max exclude null
sql max return null
sql max nulls last
sql where max value
sql if not null then max
coalesce sql
sql max date
sql min

This is grossly oversimplified, but:

I have a table, something like the following:

CREATE TABLE Table1
    ([ID] int, [USER] varchar(5), [DATE] date)
;

INSERT INTO Table1
    ([ID], [USER], [DATE])
VALUES
    (1, 'A', '2018-10-01'),
    (2, 'A', '2018-09-01'),
    (3, 'A', NULL),
    (4, 'B', '2018-05-03'),
    (5, 'B', '2017-04-01'),
    (6, 'C', NULL)
;

And for each user, I wish to retrieve the whole row of details where the DATE variable is minimal.

SELECT T.USER FROM TABLE1 T
WHERE T.DATE = (SELECT MIN(DATE) FROM TABLE1 T1 WHERE T1.USER = T.USER)

Works great, however in the instance there is no row with a populated DATE field, there will be a row with a NULL, like the final row of my table above, which I also wish to select.

So my ideal output in this case is:

(2, 'A', '2018-09-01'),
(5, 'B', '2017-04-01'),
(6, 'C', NULL)

SQL fiddle: http://www.sqlfiddle.com/#!9/df42b5/6

I think something could be done using an EXCLUDE statement but it gets complex very quickly.

You may try with row_number()

demo

    select * from
    (select *, row_number() over(partition by [user] order by [user],case when 
     [date] is null then 0 else 1 end desc,[date]) as rn
     from Table1)x where rn=1

SQL> SELECT COUNT(*) T_CUST FROM CUST; T_CUST 5 EX3_62 Write a query to display minimum cost of product. SQL> Write a query to display product name, sale date and total amount collected for the product. NULL ( count all selected rows using *; including duplicates and rows with NULLS) PNAME SALEDT  MIN is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions. A. Simple example. The following example returns the lowest (minimum) tax rate. The example uses the AdventureWorks2012

use union and and co-related sub-query with min() function

     CREATE TABLE Table1 (ID int, usr varchar(50), DATE1 date)
;

INSERT INTO Table1 VALUES
    (1, 'A', '2018-10-01'),
    (2, 'A', '2018-09-01'),
    (3, 'A', NULL),
    (4, 'B', '2018-05-03'),
    (5, 'B', '2017-04-01'),
    (6, 'C', NULL)
;



select * from Table1 t  where 
   DATE1= (select min(date1) from Table1 t1 where t1.usr=t.usr
         ) and date1 is not null
           union
select * from Table1 t where date1 is  null
and t.usr not in ( select usr from Table1 where date1 is not null)

DEMO

ID  usr     DATE1
2   A   01/09/2018 00:00:00
5   B   01/04/2017 00:00:00
6   C   

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  Summary: in this tutorial, you will learn how to use the SQL MIN function to get the minimum value in a set. The MIN function returns the minimum value in a set of values. The MIN function ignores the NULL values. The following is the syntax of the MIN function: The ALL modifier instructs the MIN function to find the minimum value in all values

You can use GROUP BY and JOIN to output the desired results.

select t.Id
    , x.[User]
    , x.[MinDate] as [Date]
from
(select [User]
   , min([Date]) as MinDate
from table1
group by [User]) x
   inner join table1 t on t.[User] = x.[User] and (t.[Date] = x.[MinDate] or x.[MinDate] is null)

SELECT Dates. SalesOrderID IS NULL; This example first finds the minimum date in the table and difference between the minimum and maximum dates. The SQL MIN() and MAX() Functions The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.

You can use a Common Table Expression:

;WITH chronology AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY [USER] 
            ORDER BY ISNULL([DATE], '2900-01-01') ASC
        ) Idx
    FROM TABLE1
)
SELECT ID, [USER], [DATE]  
FROM chronology
WHERE Idx=1;

Using a CTE in this solution simplifies the query improving its readability, maintainability and extensibility. Furthermore, I expect this approach to be optimal in terms of performance.

SQL also has column functions that you can specify in a select list to produce a of non-null expression values Min(expression) Minimum of non-null expression  A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one

It then determines the highest and lowest boundary point values in the partitioned table, which it will DATE = ( SELECT TOP 1 CAST(value AS DATE) FROM  Within the WHERE clause lies many possibilities for modifying your SQL statement. Among these possibilities are the EXISTS, UNIQUE, DISTINCT, and OVERLAPS predicates. Here are some examples of how to use these in your SQL statements. EXISTS You can use the EXISTS predicate in conjunction with a subquery to determine whether the subquery returns any …

as decimal yyyymmdd Date Pattern Lets you define the syntax pattern for Date data. Lowest weight • Highest weight Highest weight • (Default)Use prefixinboth Userdefined NULL Select Statement Post String Defines the custom SQL string  SELECT ISNULL( DOB , '') Which will return. ''. if the value is NULL. A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0, which in SQL Server is implicitly an integer representing the number of days since 1900-01-01. share.

MIN will return the smallest non-NULL value. The query won't throw an error, it will simply ignore the NULL values. SQL> with x as ( 2 select null dt from dual union all 3 select sysdate Assume that some of the dates are null. This SQL tutorial explains how to use the SQL MIN function with syntax and examples. The SQL MIN function is used to return the minimum value of an expression in a SELECT statement. OR the syntax for the MIN function when grouping the results by one or more columns is: Expressions that are not encapsulated within the MIN function and must be

Comments
  • Is this really SQL Server? SQL Server's quote operator isn't a backtick (`) it's brackets ([]). This smells like MySQL.
  • Apologies. Yes I am in SQL Server, but the code from above was copied from the SQL fiddle.
  • SQL Fiddle can SQL Server though.
  • The answers here will give you the result you want, but i wanted to explain why what you have isn't working. NULL will always have the lowest value in a range. I.e. the MIN of the values 1,0,-1,NULL,-700 will be NULL. The difference is, however, that NULL = NULL will always evaluate to "Unknown" (infact {expression} = NULL will always be unknown). As a result, this isn't true and thus any relevant rows will not be returned.
  • Thanks both! Larnu, thanks for the explanation. I realised it was to do with checking NULL = NULL which didn't seem to return as TRUE.
  • the order by clause seems to be a bit more complicated than necessary
  • I don't think this will produce the correct answer, the Null date entry on A will still be included.