Search code examples
sqlt-sqlsql-server-2014

Compare the column values where there are null and not null values

I want to compare the values from a table where there are null and not null values for the same ID ( If not null then I want minimum of two not null values). IF all the values are null for the given ID, I want the values to be displayed as null for the ID. Input 

ID     Amount                           
1        Null 
1        Null                            
1        Null                            
1        500                              
1        600 
1        700
2        Null                             
2        Null 
2        Null
2        Null 
2        Null 
3        Null
3        Null
3        300
3        600
3        200

Expected output

ID          Amount
 1           500 (min Not null value)
 2           Null 
 3           200  (Min Not null value)
Solution

  • Simple grouping will do the trick:

    select t.id, min(t.amount)
from table t
group by t.id