Selecting the Lowest Ranked and most recent record

[psycoperl]

Good Afternoon,

I am trying to select a SINGLE test record for each person, we want to take their most recent pass and if they do not have any passes but have failing records then their most recent FAIL. I tried to use the code below but am still getting multiple records for some people, if you look at the sample data Person N they have taken the exam 3 times I should only get the July 28 2005 record not the other two.

Any Suggestions?
Thank you in advance for your assistance

SELECT WEF.PERSON, WEF.[Test Dt], Format([WE1].[Score],"00") AS s1, Format([WE2].[Score],"00") AS s2, Format([WE3].[Score],"00") AS s3,
 Format([WEF].[Score],"00") AS sF, WEF.[Ltr Score], WEF.[ESL Indic] AS SecLang, Min(WEF.Hirearchy) AS MinOfHirearchy
FROM  WE3 RIGHT JOIN (WE2 RIGHT JOIN ( WE1 RIGHT JOIN  WEF ON WE1.DATAKEY = WEF.DATAKEY) ON WE2.DATAKEY = WEF.DATAKEY) ON WE3.DATAKEY = WEF.DATAKEY
GROUP BY WEF.ID, WEF.Last, WEF.[First Name], WEF.Middle, WEF.[Test Dt], Format([WE1].[Score],"00"), Format([WE2].[Score],"00"), 
Format([WE3].[Score],"00"), Format([WEF].[Score],"00"), WEF.[Ltr Score], WEF.[ESL Indic]
ORDER BY WEF.Last, WEF.[First Name], WEF.Middle, First(WEF.[Test Dt]) DESC;

SAMPLE DATA

PERSON	[Test Dt]	S1	S2	S3	SF	SCORE	SecLang	MinOfHirearchy
A	17-Aug-01	04	03		07	PASS	N	1
B	22-Dec-09	02	02		04	FAIL	Y	2
C	12-Aug-10	04	04		08	PASS	N	1
D	05-Feb-07	03	04		07	PASS	N	1
E	02-Jul-09	03	04		07	PASS	N	1
F	17-May-07	04	03		07	PASS	N	1
G	20-May-10	02	02		04	FAIL	Y	2
H	18-Mar-10	04	04		08	PASS	N	1
I	27-Jul-06	04	04		08	PASS	N	1
J	07-Jul-10	03	03		06	FAIL	N	2
K	04-Mar-10	04	04		08	PASS	N	1
L	22-May-03	03	03		06	FAIL	N	2
M	05-Jun-06	02	02		04	FAIL	N	2
N	28-Jul-05	04	04		08	PASS	N	1
N	17-May-05	03	03		06	FAIL	N	2
N	18-Jan-05	03	03		06	FAIL	N	2
O	09-Aug-02	04	04		08	PASS	Y	1
P	01-Aug-06	03	04		07	PASS	N	1

 

[MUKESHY12390]

Perhaps...this

 select t.PERSON, t.[Test Date], t.SCOREfrom T
inner join (
    select PERSON, max(t.[Test Date]) as MaxDate
    from T
    group by PERSON
)  tm on t.PERSON = tm.PERSON and t.[Test Date] = tm.MaxDate
ORDER BY t.PERSON ASC

 

[MUKESHY12390]

this looks much easier to read

SELECT t.PERSON, t.[Test Date], t.SCORE
FROM T INNER JOIN (SELECT PERSON, max(t.[Test Date]) AS MaxDate FROM T GROUP BY PERSON)  AS tm ON (t.PERSON = tm.PERSON) AND (t.[Test Date] = tm.MaxDate)
ORDER BY t.PERSON;