How to get MAX DATE With NULL VALUE?
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 I’m using the following: select id, Parent, Date From Table T1 Where Date = (select Max(Date) from Table T2 where T1.id= T2.id ) Which 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 do not consider rows with NULL values, which is why to get SQL server max date you can use a WINDOW FUNCTION to enumerate rows and then get the 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