SQL - getting the last record of the day in the last 7 days

Question

I insert a record every hour on my desk. Now I need to get the latest record for every day in the last 30 days.

Here is my table:

pl_scores: { score_id: 'BIGINT(255) PRIMARY KEY AUTO_INCREMENT' , pid: 'INT(100)' , score: 'INT(255)' , rank: 'INT(50)' , city_cnt: 'INT(10)' , updatedAt: 'DATETIME' } 

Since there are 24 entries for each day, I don’t understand how to get the last one for that day only. Any help would be appreciated.

Answer 1

Step 1 - Divide them into groups by day.
Step 2 - Select the timestamp of the last record for each group.
Step 3 - Go back and get records matching these timestamps.

 SELECT pl_scores.* FROM pl_scores INNER JOIN (SELECT MAX(updatedAt) AS maxUpdatedAt FROM pl_scores GROUP BY DATE(updatedAt)) as Lookup ON Lookup.MaxUpdatedAt = pl_scores.updatedAt 

Note. This assumes that each entry has a different value in updatedAt. If they are not unique, and several records are tied to the most recent one on any day, all related records are returned.