madflanderz madflanderz - 3 months ago 5
MySQL Question

MYSQL - Find the 3 best rated records for each day

Given this MYSQL table of images i want the best rated records for each day.

id created_at rateScore
--------------------------------------
1 2014-06-16 12:30:00 100
2 2014-06-16 13:30:00 200
3 2014-06-16 14:30:00 50
4 2014-06-16 15:30:00 20
5 2014-06-16 16:30:00 0
6 2014-06-17 14:30:00 100
7 2014-06-17 14:31:00 90
8 2014-06-18 14:31:00 100


Expected result:
Show only the 3 best rated images for each day.

id created_at rateScore
--------------------------------------
1 2014-06-16 12:30:00 100
2 2014-06-16 13:30:00 200
3 2014-06-16 14:30:00 50
6 2014-06-17 14:30:00 100
7 2014-06-17 14:31:00 90
8 2014-06-18 14:31:00 100


I tried a lot with sub selects etc. but i'm starting to become frustrated. The problem seems easy but is hard to solve for me. Maybe someone has a hint how to solve this.

Thanks in advance and have a nice day

Answer

You can achieve the result with the help of MySQL user defined variables

SELECT 
t.id,
t.created_at,
t.rateScore
FROM 
(
    SELECT 
    *,
    IF(@sameDate = DATE(created_at), @rn := @rn + 1,
       IF(@sameDate := DATE(created_at), @rn := 1, @rn := 1)
    ) AS row_number
    FROM your_table 
    CROSS JOIN (SELECT @sameDate := '0000-00-00', @rn := 1) var
    ORDER BY created_at,rateScore DESC 
) AS t
WHERE t.row_number <= 3
ORDER BY t.created_at, t.rateScore DESC

In order to get X entries for each day just change this line (WHERE t.row_number <= 3) like below:

WHERE t.row_number <= X

Note: I guess a composite index on (created_at,rateScore) would play with the performance boost up.

If you don't have any then you can create and measure the performance variation:

ALTER TABLE `your_table` ADD INDEX `idx_table_creaed_at_rateScore` (
    `created_at`,
    `rateScore`
);