CODEWITHSUNDEEP
sql
FullStack-.NetMVC-BizMule-Dev
FullStack-.NetMVC-BizMule-Dev

Reputation: 803

How to get minimum value from the SQL table?

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

Upvotes: 6

Views: 3054

Answers (4)

Emoire
Emoire

Reputation: 49

You can also do a simple check for 'NULL'. I didn't return an "ID" in the example since the "ID" seems meaningless in the example schema/query given. 

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

http://sqlfiddle.com/#!3/d5fca/11

Upvotes: 0

aleroot
aleroot

Reputation: 72636

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

Upvotes: 3

FullStack-.NetMVC-BizMule-Dev
FullStack-.NetMVC-BizMule-Dev

Reputation: 803

I think some of them are given the answer. but the final result working with below query 

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

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

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

Upvotes: 5

Related Questions