SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 cte to select latest date and value unless null
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrBloom
Starting Member

United Kingdom
32 Posts

Posted - 09/30/2013 :  17:10:11  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/30/2013 :  17:22:32  Show Profile  Reply with Quote
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

United Kingdom
32 Posts

Posted - 09/30/2013 :  17:38:09  Show Profile  Reply with Quote
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

United Kingdom
32 Posts

Posted - 10/01/2013 :  05:43:36  Show Profile  Reply with Quote
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

Bangladesh
36 Posts

Posted - 10/01/2013 :  06:56:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000