CODEWITHSUNDEEP
mysqlsqldatabase
Kevin Vermaat
Kevin Vermaat

Reputation: 311

MySQL rowrank based on ORDER BY on 2 columns

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.

Upvotes: 3

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

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".

Upvotes: 1

Related Questions