获取图像排名

Question

I am using the following code below to get the image rank from my table. So far everything works great, but the problem is, if 2 or more images has similar points, they get similar ranks. What I want is if 2 images has similar points, I want to rank them based on the date these images where submitted.

SELECT  uo.*, 
( SELECT  COUNT(DISTINCT ui.total_points)
FROM    photo_list ui
        WHERE   ui.total_points>= uo.total_points
        ) AS rank
FROM    photo_list uo

Here is what my table looks like

id  | photo_id | total_points
1      432cfc        1
2      fsd324       123
3      gd43ds        5

Answer 1

You can add more conditions to the comparison:

SELECT  uo.*, 
        (SELECT COUNT(*)
         FROM    photo_list ui
         WHERE   ui.total_points > uo.total_points OR
                 ui.total_points = uo.total_points AND ui.submitted_date  >= uo.submitted_date
        ) AS rank
FROM    photo_list uo

I'm not sure what order you want the ranking in for the submitted date, but either >= or <= should work. Note that I also changed the COUNT(). Counting just the points submitted would not make sense if you are including other features of the data.