CODEWITHSUNDEEP
sqlms-access
Nelson Tatius
Nelson Tatius

Reputation: 8043

How to get the second best value

Assume we have two tables:

Players(player_id int)
GameScores(player_id int, session_id int, score int)

How can we query the second highest-score session for every player?

For example if

Players
1
2

GameScores
(player_id, session_id, score)
1 1 10
1 2 20 
1 3 40
2 4 10
2 5 20

Then result would be
(player_id, session_id)
1, 2
2, 4

Upvotes: 3

Views: 971

Answers (4)

fthiella
fthiella

Reputation: 49069

I took a different approach... I am not sure if this is better than other answers, but i wanted to solve it this way:

SELECT
  GameScores.player_id,
  GameScores.session_id,
  GameScores.score
FROM
  GameScores
WHERE
  GameScores.score=
    (select max(score) from GameScores GameScores_2
     where GameScores.player_id = GameScores_2.Player_ID
     and GameScores_2.Score<
       (select max(score) from GameScores GameScores_1
        where GameScores_1.player_id = GameScores.player_id));

Upvotes: 0

Marc
Marc

Reputation: 16512

Can you try this

     SELECT GameScores.player_id, GameScores.session_id 
     FROM (
        SELECT player_id,MAX(score) as SecondScore
        FROM GameScores g
        WHERE score < (SELECT Max(Score) FROM gameScore where gameScore.player_id = g.player_id)
        GROUP BY player_id 
        ) x
        INNER JOIN GameScores ON x.player_id = gamescore.player_id 
          AND x.SecondScore = gamescore.score

This is the query that select the second high score for each player

SELECT player_id,MAX(score) as SecondScore
            FROM GameScores g
            WHERE score < (SELECT Max(Score) FROM gameScore where gameScore.player_id = g.player_id)
            GROUP BY player_id

You can't group by session in this query. So that's why you need to put this in a subquery and join it to gamescore to get the session_id

Upvotes: 4

Saic Siquot
Saic Siquot

Reputation: 6513

select player_id, first(session_id) as session_id
from 
   GameScores inner join (
   select player_id, max(score) as secondscore
   from 
      GameScores left join (
      select player_id, max(score) as firstscore
      from GameScores
      group by player_id
   ) as NotThisOnes on GameScores.player_id = NotThisOnes.player_id
                   and GameScores.score = NotThisOnes.firstscore
   where NotThisOnes.player_id is null
   group by player_id
) as thisare on GameScores.player_id = thisare.player_id
            and GameScores.score     = thisare.secondscore
group by player_id

Upvotes: 0

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

Here is code snippet for Oracle sql

select tbl.player_id,tbl.session_id from
(select p.player_id,g.session_id,g.score,rank()  over (partition by p.player_id order by score desc) rnk   from players p,
gamescores g 
where p.player_id = g.player_id) tbl
where tbl.rnk = 2;

Upvotes: 0

Related Questions