CODEWITHSUNDEEP
phpmysqlsqldifference
Lee Cheung
Lee Cheung

Reputation: 101

SQL - Select the top difference between 2 columns

I am looking for some queries to get the top difference between 2 columns, and return the column "name" and the difference between them.

I am using PHP (PDO) to do this with my MySQL database.

This is my table:

Table name = players.

+--------+--------+--------+
|  name  | score1 | score2 |
+--------+--------+--------+
| bob    |     10 |      5 |
| sarah  |      3 |      1 |
| george |      2 |      9 |
+--------+--------+--------+

As you can see, Bob has a difference of 5. Sarah has a difference of 2. And George has a difference of 7.

In this case, George has the highest difference between the 2 scores. I therefore want to get George's name and the difference between the 2 scores.

So on my page, I should print the string: George (+7)

Also, if 2 or more has the same difference, it should pick the one with the highest score1. If multiple people have the same difference and score1 then it should just pick any of them, it does not matter.

But how can I select the difference between these?

I have not found any documentation for this.

Upvotes: 1

Views: 135

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use order by and limit:

select t.*, (score2 - score1) as diff
from t
order by abs(score2 - score1) desc, score1 desc
limit 1;

Upvotes: 5

Related Questions