This is my table:

+--------+-------+--------+--------+
| player | points| score1 | score2 |
+--------+-------+--------+--------+
|      1 |   12  |   9    |    1   |
|      2 |   12  |   14   |    2   |
|      3 |   10  |   12   |    3   |
|      4 |   9   |   10   |    4   |
|      5 |   8   |   10   |    4   |
+--------+-------+--------+--------+

To get the second highest value, I used this query:

SELECT player,points FROM players
WHERE points= (SELECT DISTINCT(points) FROM players as p1
WHERE (SELECT COUNT(DISTINCT(points))=2 FROM players as p2
WHERE p1.points<= p2.points)) ORDER BY player

It works perfect. But now I want to get is with two conditions:

Condition1: points DESC

Condition2: (score1-score2) DESC

This query works fine to get the player with the second highest value (Checking points and score1):

SELECT player,points FROM players

WHERE points= (SELECT DISTINCT(points) FROM players as p1
WHERE (SELECT COUNT(DISTINCT(points))=2 FROM players as p2
WHERE p1.points<= p2.points))

AND

WHERE score1= (SELECT DISTINCT(score1) FROM players as p1
WHERE (SELECT COUNT(DISTINCT(score1))=2 FROM players as p2
WHERE p1.score1<= p2.score1))

ORDER BY player

How can I get the player with the second highest value using two conditions (points DESC AND (score1-score2) DESC)?

1 Answers

2
Nick On Best Solutions

This query will do what you want. It JOINs players to itself, looking for all players in the second table who would rank higher than the player in the first table. In this case ranking is based first on points, then in case of equal points on the difference between score1 and score2. The second ranked player is the one who has one player with higher rank than they do:

SELECT p1.* 
FROM players p1
JOIN players p2 ON p2.points > p1.points
                OR p2.points = p1.points AND (p2.score1 - p2.score2) > (p1.score1 - p1.score2)
GROUP BY p1.player
HAVING COUNT(p2.player) = 1

Output:

player  points  score1  score2
1       12      9       1

You can also write your original query in a similar fashion, using p2.player < p1.player as a condition to separate players with equal points (simulating the effect of your ORDER BY player):

SELECT p1.* 
FROM players p1
JOIN players p2 ON p2.points > p1.points
                OR p2.points = p1.points AND p2.player < p1.player
GROUP BY p1.player
HAVING COUNT(p2.player) = 1

Output:

player  points  score1  score2
2       12      14      2

Demo on dbfiddle