0
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
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
anonprophet
  • 97
  • 1
  • 2
  • 11

2 Answers2

1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

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
Philip H.
  • 104
  • 3
  • 1
    How will you handle the case when `deaths` happens to be zero for a given record? – Tim Biegeleisen Mar 17 '15 at 09:30
  • Fixed it. Now if deaths = 0 we will use null for the devision. Division with null gives a null again. If the devision gives null we set the return value to 99999999999. (Choosed it by assumtion it will always be the highst value and able to represent infinity.) Not the nicest solution but should work. – Philip H. Mar 17 '15 at 10:04