How to select most recent record update from multiple records? 1

[dkmidi]

Hi!

I was wondering how I can do the following:

I have a table that logs different statuses for files. First I filter a column for “Leave Status” only.

Example:

Jan 1 Bob Pending Leave Status
Feb 1 Bob Off work – planning Leave Status
Mar 1 Bob At work – Temporary Leave Status
Apr 1 Jim Pending Leave Status
May 1 Jim At work – Full-time Leave Status
Jun 1 Jim Off work – Sick leave Leave Status

How can I just retrieve the most recent update for each person? So that I get the following result? I want to be able to answer the question: As of this moment, what are the leave statuses for each person?

Desired Result:

Mar 1 Bob At work – Temporary
Jun 1 Jim Off work – Sick Leave

I have tried to use MAX date, then Group By person but I can’t find the right answer for that 3rd column display. Two columns of Date and Person are simple but getting the third colum is frustrating. If, on the 3rd column, I Group By, it shows me all the entries, and using MIN, MAX, or FIRST aren’t right either.

A secondary need is then to have a count of each. I’m guessing I can create a second query to count the totals.

Results (using simplified data above) – I don’t want to count any of the entries prior to the single most recent entry.

Pending 0
At work – Temporary 1
Off work – Sick Leave 1
Off work – Planning 0

Anyone have some suggestions on how to approach this?

Thanks!

Derek

 

[PHV]

You may try something like this:
SELECT A.*
FROM yourTable As A
WHERE A.yourDateField=(SELECT Max(B.yourDateField) FROM yourTable As B WHERE B.yourPersonField=A.yourPersonField AND B.yourStatusField='Leave Status')
AND A.yourStatusField='Leave Status';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244