CODEWITHSUNDEEP
sqlms-accessms-office
Chris Boden
Chris Boden

Reputation: 219

Selecting records with the most recent date for each unique identifier

To start, I've been scouring the internet for the last four hours and have come across a few similar issues (most here on stack overflow) with solutions that look like they could work, but did not (most of them used a single table).

I have two tables - Personnel and Tests. Personnel contains information about each person indexed by SSN. Such as:

ID   SSN          PersonName
101  111-11-1111  Joe
102  222-22-2222  Bob
103  333-33-3333  Jebadiah

Tests is a table that contains information about tests each person has taken. These tests are related to the Personnel table by SSN. Each person can have any number of tests:

ID   SSN          TestDate  Score
201  111-11-1111  1/1/2013  95.7
202  111-11-1111  2/7/2013  75.2
203  222-22-2222  1/9/2013  85.6
204  333-33-3333  5/6/2013  79.9
205  333-33-3333  4/8/2013  88.8

What I need to do is select the most recent test date and score for each person. I'm looking for a resultset that looks like this:

SSN          PersonName  TestDate  Score
111-11-1111  Joe         2/7/2013  75.2
222-22-2222  Bob         1/9/2013  85.6
333-33-3333  Jebadiah    5/6/2013  79.9

I've been trying a lot of joins and such, but I can't seem to get it to retrieve only ONE record for each SSN AND select the fields I want. Everything will select either every test and the fields I want or one test but only the SSN.

I'm hoping it's a simple solution that will make me smack my forehead.

Upvotes: 2

Views: 4175

Answers (1)

John Woo
John Woo

Reputation: 263693

SELECT  a.SSN, a.PersonName, b.TestDate, b.Score
FROM    ((Personnel AS a
        INNER JOIN Tests AS b
            ON a.SSN = b.SSN)
        INNER JOIN
        (
            SELECT  SSN, MAX(TestDate) AS MAX_DATE
            FROM    Tests 
            GROUP   BY SSN
        ) AS c  ON  b.SSN = c.SSN AND
                b.TestDate = c.MAX_DATE)

UPDATE 1

There are many ways to solve this problem but the solution I'm used to do is like this. Normally, the join between Personnel and Test will give all records but that's not exactly what you want. So I joined it with a subquery which gets the latest TestDate for every SSN and the result of the subquery is then joined back on the two tables provided that it matched on two conditions: SSN and TestDate.

Upvotes: 3

Related Questions