How to get the latest date for each person achieved level 1

[zx3]

Hi All,

I have a table as following

RecDate	name	level	points
20140601	A	1	123
20140601	B	2	22
20140603	A	1	51
20140604	A	3	58
20140604	C	1	15

The result of the query i m expecting is the following

RecDate	name	level	points
20140603	A	1	51
20140604	C	1	15

the goal is to get the latest date for each person who achieved level 1, those at other levels will be left out, i thought it could be a inner join query but i just can't get it right, please help

[recyan]

Am assuming, you have the RecDate field set as Date Type & have different names for Name & Level Fields.
One way could perhaps be :

SELECT 
    tblRecords.NameF, 
    Max(tblRecords.Recdate) AS MaxOfRecdate, 
    tblRecords.Level, 
    Last(tblRecords.Points) AS LastOfPoints
FROM 
    tblRecords
GROUP BY 
    tblRecords.NameF, tblRecords.Level
HAVING 
    (((tblRecords.Level)=1));

The second way could perhaps be :
Save below as Query2

SELECT 
    tblRecords.NameF, 
    Max(tblRecords.Recdate) AS MaxOfRecdate
FROM 
    tblRecords
WHERE 
    (((tblRecords.Level)=1))
GROUP BY 
    tblRecords.NameF;

Run this query :

SELECT 
    Query2.NameF, 
    Query2.MaxOfRecdate, 
    tblRecords.Recdate, 
    tblRecords.NameF, 
    tblRecords.Level, 
    tblRecords.Points
FROM 
    tblRecords 
    INNER JOIN 
    Query2 
    ON 
    (tblRecords.NameF = Query2.NameF) 
    AND 
    (tblRecords.Recdate = Query2.MaxOfRecdate);

Note : Pls pay attention to my signature.


Thanks

[zx3]

Thanks recyan,

I tried both ways, they work fine and i actually combined the 2 queries for the 2nd one

SELECT tblRecords.NameF, tblRecords.Recdate, tblRecords.Level, tblRecords.Points
FROM tblRecords INNER JOIN
 (SELECT tblRecords.NameF, Max(tblRecords.Recdate) AS MaxOfRecdate 
FROM tblRecords WHERE (((tblRecords.Level)=1)) GROUP BY tblRecords.NameF)  AS Query2 
ON (tblRecords.NameF=Query2.NameF) AND (tblRecords.Recdate=Query2.MaxOfRecdate);

It's great that i got help that quick!!

[recyan]

Glad you got things working.

Thanks.