MYSQL - Find the 3 best rated records for each day

Question

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

`SELECT * FROM table WHERE created_at = 'date' ORDER BY rateScore DESC LIMIT 0,3` — Derek Pollard, Aug 17 '16 at 08:59
Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Tim Biegeleisen, Aug 17 '16 at 08:59
@You That won't work, because he wants the top 3 per _group_, not from the entire result set. — Tim Biegeleisen, Aug 17 '16 at 09:00
@You `Show only the 3 best rated images for each day.` ... is this not clear to you? — Tim Biegeleisen, Aug 17 '16 at 09:02
....is it not clear that query will work for what he wants? He asked for a hint not the whole thing done. — Derek Pollard, Aug 17 '16 at 09:03
http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category — Dhaval Bhavsar, Aug 17 '16 at 09:06

jarlh · Answer 1 · 2016-08-17T09:32:45.560

0

Have a correlated sub-query that returns the number of rows on same date with a higher score. If less than 3, return!

select * from tablename t1
where (select count(*) from tablename t2
       where DATE(t2.created_at) = DATE(t1.created_at)
         and t2.rateScore > t1.rateScore) < 3

Note: Will return 4 rows if there's a tie in third place. (E.g. 100, 80, 70, 70.)

edited Aug 17 '16 at 09:32

answered Aug 17 '16 at 09:06

jarlh

42,561
8
45
63

Thanks jarlh, this is working. The mysql query looks like this: `select * from bombings t1 where (select count(*) from bombings t2 where DATE(t2.created_at) = DATE(t1.created_at) and t2.rateScore > t1.rateScore) < 3 ORDER BY DATE(t1.created_at), t1.rateScore` – madflanderz Aug 17 '16 at 09:30
I am just curious what if there are more than 3 records? And `and t2.rateScore > t1.rateScore` I doubt this condition. Shouldn't it return records with the lowest rateScore? – 1000111 Aug 17 '16 at 09:35
The sub-query counts the number of rows with a higher score (on same date). If there are more than 3 rows returned from the sub-query, do not return from t1. – jarlh Aug 17 '16 at 09:37
I tried the query with 5000 records and it needs 60s to execute on my local machine. My real application has 100000 records, so i think i can't use it "live". I'm thinking about to do this query everyday and then save the result in another table. – madflanderz Aug 17 '16 at 09:44
That's horrible performance. Check out the other answers as well, maybe you'll find something performing much better. – jarlh Aug 17 '16 at 09:51

score 0 · Answer 2 · answered Aug 17 '16 at 09:25

0

Please use below query

select r.* from rate as r 
where r.created_at IN 
(select  group_concat(r1.id) from rate as r1 
where r1.created_at = r.created_at order by r1.rateScore 
desc limit 0,3 ) group by r.id

answered Aug 17 '16 at 09:25

Dhaval Bhavsar

495
5
17

hehe, this was my first try but i had this error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – madflanderz Aug 17 '16 at 10:41

score 0 · Answer 3 · answered Aug 17 '16 at 09:37

MySQL lacks analytics functions, the funtionality you are looking for. But you may find workarround using variables. Something like:

SELECT id,
       date,
       rateScore
  FROM (SELECT    t.id,
                  t.date,
                  t.rateScore,
                  @curRank :=  IF(date=@last_date, @curRank + 1, @curRank := 1) AS rank,
                  @last_date := date
        FROM      (SELECT * FROM table ORDER BY date) t, 
                  (SELECT @curRank := 0) r) aux
WHERE aux.rank <= 3

I have not really tested the query but you may get the general idea.

1000111 · Accepted Answer · 2016-08-17T09:47:48.470

0

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`
);

edited Aug 17 '16 at 09:47

answered Aug 17 '16 at 09:41

1000111

13,169
2
28
37

This nearly working, only in the sub select the `ORDER BY DATE(created_at)` was wrong. Thank you really much. The full query is now like this: `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 bombings CROSS JOIN (SELECT @sameDate := '0000-00-00', @rn := 1) var ORDER BY DATE(created_at),rateScore DESC ) AS t WHERE t.row_number <= 3 ORDER BY DATE(`t`.`created_at`) DESC, rateScore DESC` – madflanderz Aug 17 '16 at 10:28
This wasn't wrong indeed. By the way, glad to hear that it worked for you. – 1000111 Aug 17 '16 at 10:30
This query needs 4sec for all 100000 records, much better then the other solution. – madflanderz Aug 17 '16 at 10:31
without index it is around 1 sec more, not a big difference. – madflanderz Aug 17 '16 at 10:39
`100000` is not that big. For larger data the performance difference will be visible. By the way, you can practice marking answer as accepted if it works for you. It's a good practice indeed. – 1000111 Aug 17 '16 at 10:40

MYSQL - Find the 3 best rated records for each day

4 Answers4