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 |
|
cgatlan
Starting Member
3 Posts |
 Posted - 2014-08-29 : 10:17:37
|
Hi, I'm new here. Hoping you guys can help me out..Let's say I have a table of data as per the below.. I'm trying to extract only the green highlighted items..The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.Can anyone assist? I would be most grateful.Thank You |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-08-29 : 11:16:14
|
[code]select a.*from ( select c.*, row_number() over (partition by c.case_id order by Open DESC, case Status when 'Closed' then 1 when 'Pending' then 2 when 'Open' then 3 else 4 end) rn from Cases c ) awhere a.rn = 1[/CODE]
Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy |
 |
|
|
|
|
|
Select query to extract only latest data?