SQL Top 3 - More than 3 records shows even if ORDER BY primary key

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I'm having a hard time figuring this out.

I have a table called Tgames that looks like this
Index, Game ID, Player, Points
1, 1, Player A, 10
2, 2, Player A, 5
3, 3, Player A, 10
4, 4, Player A, 7
5, 1, Player B, 5
6, 2, Player B, 7
7, 3, Player B, 5
8, 4, Player B, 10
9, 5, Player A, 5
10, 6, Player A, 7
11, 7, Player A, 5
12, 8, Player A, 5
13, 5, Player B, 10
14, 6, Player B, 7
15, 7, Player B, 7
16, 8, Player B, 7

I'm trying to get Top 3 points for each player with this query :

SELECT T.Player, T.Points
FROM Tgames As T
WHERE T.Points In (SELECT TOP 3 X.Points
FROM Tgames As X
WHERE X.[Player] = T.[Player]
ORDER BY X.Points DESC, X.Index)
ORDER BY T.Player

But I get 4 records for Player A, and 6 records for Player B...

What am I doing wrong? Please help!
Thanks!
 
Roger said:
I'm having a hard time figuring this out.

I have a table called Tgames that looks like this
Index, Game ID, Player, Points
1, 1, Player A, 10
2, 2, Player A, 5
3, 3, Player A, 10
4, 4, Player A, 7
5, 1, Player B, 5
6, 2, Player B, 7
7, 3, Player B, 5
8, 4, Player B, 10
9, 5, Player A, 5
10, 6, Player A, 7
11, 7, Player A, 5
12, 8, Player A, 5
13, 5, Player B, 10
14, 6, Player B, 7
15, 7, Player B, 7
16, 8, Player B, 7

I'm trying to get Top 3 points for each player with this query :

SELECT T.Player, T.Points
FROM Tgames As T
WHERE T.Points In (SELECT TOP 3 X.Points
FROM Tgames As X
WHERE X.[Player] = T.[Player]
ORDER BY X.Points DESC, X.Index)
ORDER BY T.Player

But I get 4 records for Player A, and 6 records for Player B...

What am I doing wrong? Please help!
Click to expand...


A has 2 records with 10 and two with 7 while B has two with
10 an four with 7. You need to select the records by using
the field that determines which of the 7 values is the third
one?

It looks like you intended to use the Index field for that
purpose so I think you want the query to be more like:

SELECT T.Player, T.Points
FROM Tgames As T
WHERE T.Index In (SELECT TOP 3 X.Index
FROM Tgames As X
WHERE X.[Player] = T.[Player]
ORDER BY X.Points DESC, X.Index)
ORDER BY T.Player, T.Points
 
Thanks a lot!
That works fine, fast answer. I love you! :)

Marshall Barton said:
Roger said:
I'm having a hard time figuring this out.

I have a table called Tgames that looks like this
Index, Game ID, Player, Points
1, 1, Player A, 10
2, 2, Player A, 5
3, 3, Player A, 10
4, 4, Player A, 7
5, 1, Player B, 5
6, 2, Player B, 7
7, 3, Player B, 5
8, 4, Player B, 10
9, 5, Player A, 5
10, 6, Player A, 7
11, 7, Player A, 5
12, 8, Player A, 5
13, 5, Player B, 10
14, 6, Player B, 7
15, 7, Player B, 7
16, 8, Player B, 7

I'm trying to get Top 3 points for each player with this query :

SELECT T.Player, T.Points
FROM Tgames As T
WHERE T.Points In (SELECT TOP 3 X.Points
FROM Tgames As X
WHERE X.[Player] = T.[Player]
ORDER BY X.Points DESC, X.Index)
ORDER BY T.Player

But I get 4 records for Player A, and 6 records for Player B...

What am I doing wrong? Please help!
Click to expand...


A has 2 records with 10 and two with 7 while B has two with
10 an four with 7. You need to select the records by using
the field that determines which of the 7 values is the third
one?

It looks like you intended to use the Index field for that
purpose so I think you want the query to be more like:

SELECT T.Player, T.Points
FROM Tgames As T
WHERE T.Index In (SELECT TOP 3 X.Index
FROM Tgames As X
WHERE X.[Player] = T.[Player]
ORDER BY X.Points DESC, X.Index)
ORDER BY T.Player, T.Points
Click to expand...
 
Back
Top