Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 cte to select latest date and value unless null

Author  Topic 

MrBloom
Starting Member

36 Posts

Posted - 2013-09-30 : 17:10:11

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.
Thanks


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
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) AS rn
FROM MYTable
)

SELECT *
FROM cte
WHERE rn = 1


James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 17:22:32
What do you want to do if there is only one row and the value for that one row happens to be null?

If you don't want to include such rows, then add a where clause in the inner query as "WHERE value IS NOT NULL".

If you do want to include such rows, then change the order by clause to "ORDER BY COALESCE(DATE,'19000101') DESC".
Go to Top of Page

MrBloom
Starting Member

36 Posts

Posted - 2013-09-30 : 17:38:09
If there is one row which is null then I would like to keep it in the result set so I will try your second option.
#thanks
Go to Top of Page

MrBloom
Starting Member

36 Posts

Posted - 2013-10-01 : 05:43:36
Sorry, in my OP I probably implied that the DATE column could be null, but the DATE column is not null. I want to select the value from the VALUE column for the latest date and if the value in this column is null then select the value from the VALUE column for the next date in the the set as long as it is not null.
Go to Top of Page

imrul
Starting Member

36 Posts

Posted - 2013-10-01 : 06:56:21
select * from MYTable as m
inner join (select i.ID,MAX(i.Date) dt from MYTable i
where i.Value is not null
group by i.ID) s
on m.ID=s.ID and m.Date=s.dt
Go to Top of Page
   

- Advertisement -