Filtering for a unique values in a single field

  • Thread starter Thread starter David P. Donahue
  • Start date Start date
D

David P. Donahue

I have a query that returns a large number of records, all
different from one another. These records (results from
an administered test) are sorted by the date they were
entered, descending. One of the fields in the resulting
data is the Social Security Number of the test taker, so
it's unique for each individual. Is there any way to
filter the results so that only the first instance of a
record with any SSN is shown? Thus, only the most recent
test taken by any individual is shown? If possible, I'd
prefer help in the form of SQL syntax. Thank you for your
time.


Regards,
David P. Donahue
 
Yes it is possible. Try the following UNTESTED SQL statement.

SELECT *
FROM YourTable
WHERE YourDate =
(SELECT Max(tmp.YourDate)
FROM YourTable as Tmp
WHERE YourTable.SSN = Tmp.SSN)
 
SELECT *
FROM YourTable
WHERE YourDate =
(SELECT Max(tmp.YourDate)
FROM YourTable as Tmp
WHERE YourTable.SSN = Tmp.SSN)
Click to expand...

Works great, thanks!

Regards,
David P. Donahue
 
Back
Top