CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Pastor Bones
Pastor Bones

Reputation: 7361

retrieve last record of the day for the last 7 days

I am trying to retrieve the most recent record on each day for the last 7 days. However, each record returned has an identical date for updatedAt (the most recent day), but the other data does return correctly. How can I change my sql query to give me the correct date for each record?

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
WHERE pl_scores.pid = 977 ORDER BY pl_scores.updatedAt ASC LIMIT 7

Upvotes: 1

Views: 377

Answers (3)

user1118250
user1118250

Reputation: 39

You have to GROUP BY DAY(updatedAT) your results in order to get the MAX for each day - also, add the WHERE updatedAT >= DATE_SUB(updatedAt, INTERVAL 1 WEEK ) clause to be sure that you are not getting values older than a week.

Also, change the join and use the id column, in order to avoid the case where 2 records have the same updatedAt value

SELECT pl_scores.* FROM pl_scores
INNER JOIN 
(SELECT MAX(updatedAt) AS maxUpdatedAt, id FROM pl_scores 
   WHERE updatedAT >= DATE_SUB(updatedAt, INTERVAL 1 WEEK ) 
   GROUP BY DAY(updatedAt)) as Lookup ON Lookup.id = pl_scores.id
WHERE pl_scores.pid = 977 ORDER BY pl_scores.updatedAt ASC LIMIT 7

Hope this helps.

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26861

SELECT pl_scores.* FROM pl_scores
INNER JOIN (SELECT MAX(updatedAt) AS maxUpdatedAt, id FROM pl_scores WHERE updatedAT >= DATE_SUB(updatedAt, INTERVAL 1 WEEK ) 
GROUP BY DAY(updatedAt)) as Lookup ON Lookup.id = pl_scores.id
WHERE pl_scores.pid = 977 ORDER BY pl_scores.updatedAt ASC LIMIT 7

Upvotes: 0

spiffywebtools
spiffywebtools

Reputation: 77

that kind of depends on how your db stores the time/date and why/when/how that changes. how many time/date fields have you and how stored?

Upvotes: 0

Related Questions