-1
  1. I am averaging 4 columns and giving an alias score.
  2. Now, I am trying to use score alias inside GROUP_CONCAT to get rank.
  3. Everything works except when I add (SELECT GROUP_CONCAT( score ORDER BY score DESC) FROM math )) AS rank.
  4. I understand it does not work because score does not exist in the math table. But what do I need to do to make it work?

Here is my query in laravel 5.5 -

$ranking = DB::select('SELECT id, (a.addition_accuracy + a.subtraction_accuracy + a.multiplication_accuracy + a.division_accuracy)/4 as score, (SELECT GROUP_CONCAT( score ORDER BY score DESC) FROM math )) AS rank FROM math a where level = 5');

  1. I get 500 server error from above query.
  2. I am expecting below output
    {
        "id": 38,
        "score": 99.24250030517578,
        "rank": 1

    },
    {
        "id": 51,
        "score": 84.88500213623047,
        "rank": 2
    },
    {
        "id": 204,
        "score": 69.27500057220459,
        "rank": 3
    }
]```
Nazmul Islam
  • 61
  • 3
  • 7

1 Answers1

0

Try this one for your query. I changed it to checl also the score

SELECT 
  id
  , (a.addition_accuracy + a.subtraction_accuracy + a.multiplication_accuracy + a.division_accuracy)/4 as score
  ,if(@score = score,@rank:= @rank,@rank:= @rank+1) as rank
  ,  @score := score
  FROM math a,(SELECT @rank :=0) b ,(SELECT @score :=0) c
  where level = 5
  ORDER BY score DESC
LIMIT 3

As i said some data as test ground would be nice

nbk
  • 45,398
  • 8
  • 30
  • 47
  • I have just added what I expect to see – Nazmul Islam Jan 10 '20 at 20:40
  • Edited answer gives ranking perfectly but little problem. When two person have same score it gives one person a rank 1 and second person a rank 2. How do I make it so that it gives both rank 1. { "id": 38, "score": 99.24250030517578, "rank": 1 }, { "id": 356, "score": 99.24250030517578, "rank": 2 } – Nazmul Islam Jan 10 '20 at 21:10
  • I had to change the "ORDER BY score" to "ORDER BY score desc" – Nazmul Islam Jan 10 '20 at 21:14
  • Try this new changed version, it checks if the score is the same it doesn't change the rank.. – nbk Jan 10 '20 at 21:25
  • It gives error. I think it's not able to put `score` in `@score`. It breaks right when I add `@score := score` – Nazmul Islam Jan 10 '20 at 21:52
  • Worked. Instd of putting `score` alias I put the whole thing- ```SELECT id , (a.addition_accuracy + a.subtraction_accuracy + a.multiplication_accuracy + a.division_accuracy)/4 as score ,if(@newScore = (a.addition_accuracy + a.subtraction_accuracy + a.multiplication_accuracy + a.division_accuracy)/4,@rank:= @rank,@rank:= @rank+1) as rank , @newScore := (a.addition_accuracy + a.subtraction_accuracy + a.multiplication_accuracy + a.division_accuracy)/4 as newScore FROM math a,(SELECT @rank :=0) b ,(SELECT @newScore :=0) c where level = 5 ORDER BY score DESC LIMIT 3``` – Nazmul Islam Jan 10 '20 at 22:14