# Get the second highest value with two conditions

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)? On Best Solutions

This query will do what you want. It `JOIN`s `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