Retrieving the most recent record from each person

[PKI_Dave]

I have a table of scores and I'd like to obtain the most recent record for
each person, but not everyone has an entry for a particular day. For
example, if I have three users with the following scores, entered on the
following days:

Name1 10 1/1/08
Name2 20 1/1/08
Name3 30 1/1/08
Name1 11 1/8/08
Name3 31 1/8/08

I would expect to get
Name1 11 1/8/08
Name2 20 1/1/08
Name3 31 1/8/08

I've tried several queries, but I only get the names for the most recent
date, skipping anyone without a record for 1/8/08. This is a result of the
TOP 1 clause. Without this, though, I get multiple records for each person.
What I really want is the most recent score for every person in the table.

SELECT TOP 1 Player.PlayerName, Score.PlayerID, Score.ScoreDate
FROM Score RIGHT JOIN Player ON Score.PlayerID = Player.ID
ORDER BY Score.ScoreDate DESC;

Your help would be greatly appreciated.

 

[Conan Kelly]

PKI_Dave,

First create a separate query. The query would go something like this:

SELECT Score.PlayerID, MAX(Score.ScoreDate) as ScoreDate
FROM Score
GROUP BY Score.PlayerID

Save this query with a meaningful name, something like
"qryMaxDateByPlayerID"

Create a new query. Add the Score table/query and the qryMaxDateByPlaerID
query. Create joins on these 2 tables on both the PlayerID field and the
ScoreDate field. Make sure to set both of those joins to return all records
from qryMaxDateByPlaerID and only matching records from Score. Then you can
add the Player table/query and create the join between Player and Score on
PlayerID. Set the join to return all records from Score and only matching
records from Player. Add the fields that you want in your results to the
design grid.

HTH,

Conan

 

[Conan Kelly]

PS. That all could be done in one query, and from what I read, that might
speed things up as well. But if you have a low number of players and a low
number of records in the Scores table, you may not notice a difference.

I would not want to tackle an all-in-one query until I knew you had my
suggestion working first and then posted the SQL script for both queries
back here.

I have not tested my solution, but based on past experience, I think my
syntax is correct and that should provide you with the results you are
asking for.

 

[Ken Sheridan]

As Conan said it can be done in a single query. The trick is to use a
subquery to identify the latest ScoreDate for the player, putting the
subquery in the outer query's WHERE clause to restrict the rows returned to
those where the ScoreDate matches that returned by the subquery:

SELECT PlayerName, Score, ScoreDate
FROM Score AS S1 INNER JOIN Player ON S1.PlayerID = Player.ID
WHERE S1.ScoreDate =
(SELECT MAX(ScoreDate)
FROM Score AS S2
WHERE S2.PlayerID = S1.PlayerID);

Note how the two instances of the Score table are differentiated by the
aliases S1 and S2.

Ken Sheridan
Stafford, England

 

[John Spencer]

Perhaps you want the following. You might change the last INNER JOIN to a
RIGHT JOIN if there is the possibility that a player would have no related
records in the score table.

SELECT Player.PlayerID
, Player.PlayerName
, Score.Score, Score.ScoreDate
FROM (Score INNER JOIN
(SELECT PlayerID, Max(ScoreDate) LastScore
FROM Score
GROUP BY PlayerID) as L
On Score.PlayerID = L.PlayerID
AND Score.ScoreDate = L.LastScore)
INNER JOIN Player
ON Score.PlayerID = Player.PlayerID
ORDER BY Score.ScoreDate
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[PKI_Dave]

Thanks for the help on this. The query worked perfectly and I was able to
add some additional fields on my own. I wish I knew sql better, but with
help from the dicussion group, I'm learning.

Dave