CODEWITHSUNDEEP
mysql
anonprophet
anonprophet

Reputation: 97

MySQL Order By Column1 / Column2

SELECT `player`,`kills`,`deaths` FROM `stats` ORDER BY `kills` DESC LIMIT 0 , 10

how to make ORDER BY to THE BIGGEST value from (KILLS / DEATHS)

thanks

---------------------------
| player | kills | deaths |
---------------------------
| user1  | 20    | 2      | 
---------------------------
| user2  | 10    | 2      |
---------------------------
| user3  | 30    | 2      | 
---------------------------

KDR = Kill Death Ratio = Kills / Deaths

so the result order like this

1. user3 = 15

2. user1 = 10

3. user2 = 5

Upvotes: 0

Views: 89

Answers (2)

Philip H.
Philip H.

Reputation: 104

Well i guess you want also to show the k_d_ratio as a value so i would suggest this solution.

  SELECT `player`,`kills`,`deaths`,`assist`, ISNULL 
     (kill /(NULLIF(deaths,0), 
      99999999999) 
    as k_d_ratio FROM stats 
   ORDER BY `k_d_ratio` DESC LIMIT 0 , 10

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Here is a solution to your problem:

SELECT `player`,`kills`,`deaths`,`assist`
FROM `stats`
ORDER BY (
    CASE WHEN `kills` > `deaths` THEN `kills` ELSE `deaths`
)
DESC LIMIT 0 , 10

The trick is to use the CASE statement for choosing the larger of the two columns 'kills' or 'deaths' for ordering the results.

Upvotes: 1

Related Questions