How to get most recent records from table?
Siv
I have two tables Person and Employment. Person has 1 row per person,
Employment lists that person's job history where each row is an employment
period at a particular grade. Therefore there can be multiple rows per Person
in the Employment table.
What I want to do is produce a report that shows the count of staff who are
part time by gender. The Part time flag is in the employment history table,
so I want to extract a list of staff using data from the Person table and
just the most recent record for each person in the Employment table.
My first attempt went like this:
SELECT COUNT(Person.Gender) AS [Number Part Time], Person.Gender
FROM Person INNER JOIN
Employment ON Person.PersonID = Employment.fkPersonID
WHERE (Employment.PartTimeYN = 1) AND (Person.RecordIsDeletedYN = 0)
GROUP BY Person.Gender
The problem is that because there a multiple rows in the Employment table my
count is incorrect if a person has two rows of employment data where they
were part time, it is counted as 2 rather than 1.
How can I limit the rows being picked up from the Employment table to just
the most recent one. There is a field in the Employment table called
DateStarted that holds the date a particular role commenced, I just can't
figure out how to limit it??
Any help appreciated.
Siv
--
Martley, Near Worcester, UK