Select newest record for each person in table

Hello Experts,
   I have a table that I need to select the newest entry base upon a date field  for each person listed.  The requirement is that I need to select the two rows for the encounterID with the newest date (measrment_dt) for a given person (sys_id column).  I have a sample extract from the table attached.  The top table is the simple select * from table output.  I have highlighted the rows that I would need to select in my query.  The table below is the output I am seeking.  Thanks in advance for your help.

2015-09-01_14-19-57.jpg
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
You need to use the ROW_NUMBER() with PARTITIONing. Something like:
WITH Records AS (
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY SYS_ID, MEASRMNT_KIND ORDER BY MEASRMNT_DT DESC) RowNo, --assume SYS_ID identifies a person
        Sender, MBR_ID, DATA_SRC, FIRST_NM, 
        LST_M, DOB, SSN, SYS_ID, MEASRMNT_KIND, 
        MEASRMNT_DT, VALUE, HEIGHT, WEIGHT
    FROM TABLE
)
SELECT * 
FROM Records 
WHERE RowNo = 1 --Comment this line out so you can inspect the outcome of using RowNo

Open in new window

Further reading:
https://msdn.microsoft.com/en-us/library/ms186734.aspx
http://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Based on the requirements given give this a whirl..
SELECT SYS_ID, Max(MEASRMNT_DT) as most_recent_date
FROM YourTable
GROUP BY SYS_ID
ORDER BY SYS_ID

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.