sql - Selecting records with the most recent date for each unique identifier -

to start, i've been scouring internet last 4 hours , have come across few similar issues (most here on stack overflow) solutions work, did not (most of them used single table).

i have 2 tables - personnel , tests. personnel contains information each person indexed ssn. such as:

id   ssn          personname 101  111-11-1111  joe 102  222-22-2222  bob 103  333-33-3333  jebadiah 

tests table contains information tests each person has taken. these tests related personnel table ssn. each person can have 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 need select recent test date , score each person. i'm looking resultset looks 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 lot of joins , such, can't seem retrieve 1 record each ssn , select fields want. select either every test , fields want or 1 test ssn.

i'm hoping it's simple solution make me smack forehead.

select  a.ssn, a.personname, b.testdate, b.score    ((personnel         inner join tests b             on a.ssn = b.ssn)         inner join         (             select  ssn, max(testdate) max_date                tests              group   ssn         ) c  on  b.ssn = c.ssn ,                 b.testdate = c.max_date) 

update 1

there many ways solve problem solution i'm used this. normally, join between personnel , test give records that's not want. joined subquery gets latest testdate every ssn , result of subquery joined on 2 tables provided matched on 2 conditions: ssn , testdate.