积分排名系统

Question

Below is the code I have to retrieve the rank of a certain images, everything works perfectly fine. However, if two or more images has the same total points, I am getting duplicate rank results. My main goal is, if two or more images has tie total_points, rank them based on when these images were submitted. (old to recent order). How do I break the tie?

Answer 1

You can use a variable to rank by total points descending and date ascending:

set @rank = 0;
select @rank := @rank + 1 as rank,
  photo_id,
  total_points,
  date
from photo_list
order by
  total_points desc,
  date asc

SQL FIDDLE