MySQL rowrank based on ORDER BY on 2 columns

Question

The database has the table persons:

id          int
points      int
voted_on    int 
other columns

I want to know which row rank the row with id x has, rows with same points has same row rank. This query works great and fast: (but tell me if you know even better one:)

select count(*)
from persons p cross join
     (select points from persons p where p.id = x) const
where p.points > const.points;

But now I would like to upgrade the query that people with less voted_on, but same amount of points, have a better row rank than people with more voted_on. However, people with same amount of points and voted_on should have the same row rank.

Gordon Linoff · Accepted Answer · 2013-06-05T18:18:44.430

1

You need a more complex where clause:

select count(*)
from persons p cross join
     (select points, voted_on from persons p where p.id = x) const
where (p.points > const.points) or
      (p.points = const.points and p.voted_on >= const.voted_on)

I'm not sure if "better" means a lower rank or a higher rank. This gives people a higher rank if voted_on is bigger. For a lower rank, change the >= to <=.

EDIT:

I believe this works for the "lowest" row rank (as per your comment):

select count(*) + 1
from persons p cross join
     (select points, voted_on from persons p where p.id = x) const
where (p.points > const.points) or
      (p.points = const.points and p.voted_on > const.voted_on)

This will start the ranking at "1" rather than at "0".

edited Jun 05 '13 at 18:18

answered Jun 05 '13 at 17:58

Gordon Linoff

1,242,037
58
646
786

thanks a lot for the answer, but I would prefer to give them all the highest row rank, the above query gives all 3 the lowest. If there are 6 people in the database, and people 2 till 4 has the same amount of points and voted_on, how give them all the rowrank of 2 (instead of 4). Thanks again and sorry, should have said this before. And would your query fancy an index on (id, voted_on)? – Kevin Vermaat Jun 05 '13 at 18:14
Thanks for the edit, works as a charm, last question: would i need an extra index to speed it up? Got only (id, points) at the moment. – Kevin Vermaat Jun 05 '13 at 18:23
@KevinVermaat . . . An index on `points, voted_on` would probably help, as would an index on `id` (for the subquery). If you need the values for more than one "x", then ask another question. That problem might have a very different solution. – Gordon Linoff Jun 05 '13 at 18:25
the indexes you suggested, those are normal indexes or you mean (p.id, points) and (p.id, p.voted_on)? – Kevin Vermaat Jun 09 '13 at 13:35
And what is the secret behind this query? It is so fast, I do not understand, (got non-composite indexes at the moment). – Kevin Vermaat Jun 09 '13 at 13:42
@KevinVermaat . . . The indexes make the inequality count much, much faster. It is a partial index scan for each row rather than a full table scan for each row. – Gordon Linoff Jun 09 '13 at 15:39
Gordon linoff thanks again, could you take a look at: http://stackoverflow.com/questions/17026889/sql-select-query-order-by-on-2-columns-and-where-clause-big-table, I think you are the right person to ask. Not getting many answers :/ – Kevin Vermaat Jun 10 '13 at 18:48

MySQL rowrank based on ORDER BY on 2 columns

1 Answers1