asked on

Top 5 Players with no duplicates

Greetings folks,

I'm looking for a query that I really don't know how to do... Take this data:
269      Ben
255      Ben
247      Michael
245      Jim
236      Ben
234      Michael
233      Michael
227      Lee
222      Tony
215      Michael
215      Jim
214      Ben
213      Ben
212      Michael
212      Tony
208      Michael
207      Ben
206      Michael
205      Tony
201      Ben

I'm currently using this SQL Statement that produces the above data:
SELECT TOP 20
      G.Score AS 'Score',
      P.FirstName AS 'Player'
FROM
      Games G
INNER JOIN
      Players P ON G.PlayerID = P.PlayerID
ORDER BY
      Score DESC

What I want is to get the top 5 scores, but never duplicating a name. So the successful query will produce the following output:
269      Ben
247      Michael
245      Jim
227      Lee
222      Tony

Any suggestions?

ASKER CERTIFIED SOLUTION

DireOrbAnt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DireOrbAnt

Player names could be dups, you should group by IDs:
SELECT TOP 20
MAX(G.Score) AS 'Score',
P.FirstName AS 'Player'
FROM
Games G
INNER JOIN
Players P ON G.PlayerID = P.PlayerID
GROUP BY G.PlayerID
ORDER BY
MAX(G.Score) DESC

DireOrbAnt

Darn, make the last one:
GROUP BY G.PlayerID, P.FirstName

Lee W, MVP

ASKER

Just what I was looking for - and I SHOULD have figured that out on my own... thanks for the quick solution.

DireOrbAnt