CODEWITHSUNDEEP
sql
wacomi
wacomi

Reputation: 3

How to rank players highest score

I have two tables

Players
id     Player
1      Jack
2      Anna
3      Sam

Scores
id     player_id     score
1      1             500
2      1             200
3      2             300
4      2             200
5      3             750

I would like the output to look like

1   Sam     750
2   Jack    500
3   Anna    300

Currently my code looks like 

SELECT ROW_NUMBER() OVER (ORDER BY S.score DESC, P.name), P.name, S.score
FROM Players P, Scores S
WHERE P.id = S.player_id;

but my output is

1   Sam    750
2   Jack   500
3   Anna   300
4   Anna   200
5   Jack   200

I'm not sure how to get distinct player names, my attempts at using DISTINCT have not been working out. I'm just learning so apologies if this is something really obvious, i've tried to look for answers without success.

Upvotes: 0

Views: 475

Answers (4)

Luuk
Luuk

Reputation: 14920

SELECT ROW_NUMBER() OVER (ORDER BY x.score DESC, x.name), x.name, x.score
FROM (
   SELECT P.Name, Max(S.Score) as score 
   FROM Players P, Scores S 
   WHERE P.id = S.player_id
   GROUP BY P.Name ) x 
ORDER BY x.score DESC, x.Name

First get the highest score per user, than rank the stuff.

Upvotes: 1

Viresh Mathad
Viresh Mathad

Reputation: 636

You need to find highest score by each player and sort it by marks.

  1. Find the highest score by each player from scores table
  2. Get the player name from Player table and Sort it by Highest score

  3. Use Rank

    SELECT RANK() OVER (ORDER BY s.Score desc) as Rank, P.Player,S.score from (SELECT player_id, Max(Score) as score FROM Scores GROUP BY player_id) S INNER JOIN PLAYERS P ON P.id = S.player_id ORDER BY score desc

    .

Upvotes: 0

downernn
downernn

Reputation: 174

My first inclination would be to use a subquery like the other suggested answers, but the following also seems to work, at least in SQL Server:

SELECT ROW_NUMBER() OVER (ORDER BY MAX(S.score) DESC), P.name, MAX(S.score)
FROM Players P INNER JOIN Scores S ON P.id = S.player_id
GROUP BY P.Name;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you should use rank()instead of row_number() to handle ties.

Then, you should learn to use proper, explicit, standard, readable join syntax.

One method uses a subquery:

SELECT RANK() OVER (ORDER BY s.max_score DESC), p.name, s.max_score
FROM (SELECT s.player_id, MAX(s.score) as max_score
      FROM Scores s
      GROUP BY s.player_id
     ) s JOIN
     Players P
     ON P.id = S.player_id
ORDER BY s.max_score DESC, P.Name;

You are using window functions, so you can do this without GROUP BY as:

SELECT RANK() OVER (ORDER BY s.max_score DESC), p.name, s.max_score
FROM (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY s.player_id ORDER BY s.score DESC) as seqnum
      FROM Scores s
      GROUP BY s.player_id
     ) s JOIN
     Players P
     ON P.id = S.player_id
WHERE seqnum = 1
ORDER BY s.max_score DESC, P.Name;

Upvotes: 0

Related Questions