showing the last 20 results from a union query

[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

 

[ghetto_banjo]

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;

 

[pat67]

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;

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.

 

[pat67]

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.

To be more specific, i need to show the GameID for each player in
descending order.

 

[ghetto_banjo]

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;

 

[ghetto_banjo]

Oops that TOP 2 should read TOP 20

 

[pat67]

Oops that TOP 2 should read TOP 20

thanks. let me try it

 

[pat67]

Oops that TOP 2 should read TOP 20

That seems to be working. then i can just count the won and lost for
records.

Thanks

 

[ghetto_banjo]

No problem, glad it worked. those subqueries get confusing,
especially when referencing the same table as main query.