Hi I have a table which looks like this below.
I would like to select the row with the latest date where the ID is a duplicate. I have used a CTE with Row Number to select this, as below. However if the row with the latest date has a null VALUE and the next date is not null , as with ID 512 below, then I would like to skip the latest date and select the other date and VALUE. Any help would be appreciated.
ID DATE VALUE rn
511 2007-12-04 00:00:00.000 10 1
511 2004-07-28 00:00:00.000 20 2
512 2007-12-04 00:00:00.000 NULL 1
512 2004-07-28 00:00:00.000 20 2
;WITH cte AS
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) AS rn
WHERE rn = 1