How to reterive SQL server MAX DATE With NULL VALUE? Does Max work with NULL values?
Trying to get the max date from my table, i can't retrieve the value when the date is null. Sample : the following table :+----+--------+------+------------------------+ | id | parent | Date | +----+--------+------+------------------------+ | 1 | Alex | 2018-10-01 00:00:00.0000000 | | 1 | TIM | 2012-11-07 00:00:00.0000000 | | 3 | Cath | NULL | +----+--------+------+------------------------+ To get the max date im using the following: select id, Parent, Date From Table T1 Where Date = (select Max(Date) from Table T2 where T1.id= T2.id ) Wich works fine for the id = 1 But for the id = 3 it's not working The result is : 1 | Alex | 2018-10-01 00:00:00.0000000 Any ideas Why?
Aggregated functions does not consider rows with NULL values. You could use a WINDOW FUNCTION to enumerate rows and then get first row only:with ct as ( select id, Parent, Date, row_number() over (partition by id order by id, date desc) rn from tbl ) select id, Parent, Date from ct where rn = 1 order by id; id | Parent | Date -: | :----- | :------------------ 1 | Alex | 01/10/2018 00:00:00 3 | Cath | null Does Max work with NULL values: By default the functions MAX and MIN do not count NULL in their evaluation of your data. If we have a column containing only dates for instance and there is a NULL date, MAX and MIN will both ignore that value.