showing the last 20 results from a union query

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

I have a union query showing the results from pool matches

GameID Player Opponent Result
1 Joe Bob Won
2 Jim Bill Won
1 Bob Joe Lost
2 Bill Jim Lost

Obviousy a lot more games but that is the premise. What i am looking
for is to show the last 10,20 whatever results for each player. Total
record is easy, just count won and lost. But I am not sure how do get
the last 20 games for each player. Can someone help?

Thanks
 
Do you store the date/time of these matches? If not, it looks like
the only way to grab the "last" 20 matches would be via the GameID.

I am not sure how your table is setup exactly, but this should point
you in the right direction.

SELECT TOP 20 GameID, Player, Opponent, Result
FROM tblMatches
WHERE Player = 'Joe'
ORDER BY GameID DESC;
 
Do you store the date/time of these matches?  If not, it looks like
the only way to grab the "last" 20 matches would be via the GameID.

I am not sure how your table is setup exactly, but this should point
you in the right direction.

SELECT TOP 20 GameID, Player, Opponent, Result
FROM tblMatches
WHERE Player = 'Joe'
ORDER BY GameID DESC;
Click to expand...

I want to do it for every player. i do have a date, but need to use
the ID since a player normally plays 3 or 4 games a night.
 
I want to do it for every player. i do have a date, but need to use
the ID since a player normally plays 3 or 4 games a night.
Click to expand...

To be more specific, i need to show the GameID for each player in
descending order.
 
Try this out. You need to use a subquery to get Top results per
Player.

SELECT tblMatches.Player, tblMatches.Opponent, tblMatches.Result,
tblMatches.GameID
FROM tblMatches
WHERE tblMatches.GameID IN (SELECT TOP 2 GameID From tblMatches AS
Dupe WHERE Dupe.Player = tblMatches.Player ORDER BY Dupe.GameID DESC)
ORDER By tblMatches.Player, tblMatches.GameID DESC;
 
No problem, glad it worked. those subqueries get confusing,
especially when referencing the same table as main query.
 
Back
Top