3

I have the following data:

enrtryid    categoryid     score
   1             1         90.5
   2             1         90.5
   3             1         89
   4             2         70
   5             2         69
   6             3         78

I desire following result:

enrtryid    categoryid     score    rank
   1             1         90.5      1
   2             1         90.5      1
   3             1         89        2
   4             2         70        1
   5             2         69        2
   6             3         78        1

I have tried using the query 

SELECT t1.*, (SELECT COUNT(*) FROM overalltally t2 WHERE t2.score> t1.score) +1
AS rank
FROM overalltally t1
ORDER BY `rank` ASC

though it ranks all the results sequentially as I don't know where I should place the GROUP BY statement

Alexander Kimaru
  • 375
  • 4
  • 21
  • Check your spelling :`as rnk ... ORDER BY rank`. – Tim Lewis Nov 11 '15 at 15:31
  • 1
    http://stackoverflow.com/questions/24118393/mysql-rank-with-ties – Neville Kuyt Nov 11 '15 at 15:34
  • I am voting to reopen since this question is *not exactly the same* as the one cited. The OP here wants to rank results *grouped by category*. – Giorgos Betsos Nov 11 '15 at 15:52
  • 1
    @NevilleK This question is different since I want to rank per category – Alexander Kimaru Nov 11 '15 at 17:00
  • 1
    I have voted to reopen this. Based on your current query change the sub query to _(SELECT COUNT(*) FROM overalltally t2 WHERE t2.score> t1.score AND t2.categoryid = t1.categoryid)_ . But it can also be done with a variation on the suggestion by @StefanGehrig below – Kickstart Nov 11 '15 at 17:20
  • @Kickstart: That sub query is grande!! Does the work!! Thank you very much!!! – Alexander Kimaru Nov 12 '15 at 12:15
  • The down side of the sub query like that is it needs to be performed on every returned row, hence can be quite slow as the number of rows increases. – Kickstart Nov 12 '15 at 12:27

1 Answers1

1

Something like this should work.

SELECT t1.*, @rank := IF(@previous = t1.score, @rank, @rank + 1) AS rank,
  @previous := t1.score
FROM overalltally t1, (SELECT @previous := null, @rank := 0) tmp_tbl
ORDER BY rank ASC

It looks a bit tricky but actually it's a rather standard approach to work around missing ranking functionality in MySQL.

Stefan Gehrig
  • 82,642
  • 24
  • 155
  • 189
  • This ranks all the rows sequentially. How would it rank per group? – Alexander Kimaru Nov 11 '15 at 16:54
  • You can add a variable to store and check the category id as well `SELECT t1.*, @rank := IF(@previous_cat = t1.categoryid, IF(@previous = t1.score, @rank, @rank + 1), 1) AS rank, @previous := t1.score, @previous_cat = t1.categoryid FROM ( SELECT enrtryid, categoryid, score FROM overalltally ORDER BY categoryid, score DESC ) t1 CROSS JOIN (SELECT @previous := null, @rank := 0, @previous_cat := 0) tmp_tbl ORDER BY categoryid, rank ASC` – Kickstart Nov 11 '15 at 17:26