How to get minimum value from the SQL table?

Question

I have a table as 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 a min date from the above table, that's result should be Null

I was trying to write

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

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

Is there any otherway to pull Null value from the above table except the below method?

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

Answer 1

Assuming that your dbms is SQL-Server.

If you want to group by id but select all fields anyway, you can use a cte with ROW_NUMBER function:

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

http://sqlfiddle.com/#!3/cc2a4/7

Answer 2

By default the functions MAX and MIN do not count NULL in their evaluation of your data.

Try in 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