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 2012 Forums
 Transact-SQL (2012)
 Select query to extract only latest data?

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

Posted - 2014-08-29 : 10:44:37
You could use either GROUP BY or CTE \ PARTITIOn BY . To view an example of CTE and PARTITIOn BY read through this example http://www.sqlserver-dba.com/2011/05/sql-server-cte-and-partition-by.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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
) a
where
a.rn = 1[/CODE]



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page
   

- Advertisement -