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.
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 rn511 2007-12-04 00:00:00.000 10 1511 2004-07-28 00:00:00.000 20 2512 2007-12-04 00:00:00.000 NULL 1512 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 cteWHERE 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". |
|
|
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 |
|
|
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. |
|
|
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) son m.ID=s.ID and m.Date=s.dt |
|
|
|
|
|