How to get minimum value from SQL table?

Question

I have a table below

ID   Date  
1    Null  
1    Null  
1    Null  
1    02/02/2012  
1    02/03/2012  
1    02/04/2012  
1    02/05/2012

I want to take the min date from the above table, this result should be Null

I tried to write

select min(date), Id from Table group by ID

then the result 02/02/2012, but I want Null.

Is there any other way to infer the Null value from the above table other than the method below?

select top 1 date, ID from table order by date asc

+5

Software enginner Jun 15 '12 at 14:49

4 answers

Assuming your dbms is SQL Server.

If you want to group by id, but still select all the fields, you can use the function ctewith ROW_NUMBER:

WITH cte AS(
  SELECT x.*
  , RN=ROW_NUMBER()OVER(Partition By id Order By date)
  FROM Table x
)
SELECT * FROM cte 
WHERE RN=1

+5

Tim schmelter 15 . '12 15:01

, .

SELECT CASE WHEN   MIN(coalesce(date, '1900-01-01')) = '1900-01-01' THEN NULL ELSE MIN(date) END AS Date, ID
FROM table
GROUP BY ID

0

You can also do a simple check for "NULL". I did not return “ID” in the example, since “ID” seems pointless in the schema / query diagram below.

    IF (EXISTS(SELECT TOP 1 * FROM x WHERE date IS NULL)) SELECT NULL AS MinDate
    ELSE SELECT MIN(date) AS MinDate FROM x;

0

Emoire Jun 15 '12 at 21:20

aleroot · Accepted Answer · 2012-06-15T14:56:59+0000

By default, the MAX and MIN functions do not consider NULL when evaluating your data.

Try this way, should do the trick:

SELECT 
CASE WHEN MIN(COALESCE(Date, '19001231')) = '19001231' THEN NULL ELSE MIN(Date) END AS Date,
Id 
FROM X 
group by ID