Mysql query to find last entry for each of the last 7 days I have a table that contains a timestamp and a number for each row. This data is logged every 15mins on the quarter hour, 24 hours a day, but it does sometimes get missed or is delayed. I'd like to be able to extract the data for the end of each of the last 7 days including the most recent log today. I have two different queries that almost achieve this but not quite:

SELECT * 
FROM WaterDepth 
WHERE (date &gt;= DATE(NOW()) - INTERVAL 7 DAY) AND (HOUR(date)+MINUTE(date) = 68) 
ORDER BY date ASC LIMIT 7


This captures the last 7 days at 23:45, but misses any days that don't have an entry at precisely 23:45 and misses today.

SELECT a1.* 
FROM WaterDepth a1 
INNER JOIN (SELECT max(date) as max 
            FROM WaterDepth 
            GROUP BY date(date)) a2 ON a1.date = a2.max


This captures the last entry for each day regardless of what time that is and includes today, but I'm not sure how to limit it to the last seven days. So I guess I'm looking a blend of the two.

Thanks in advance
SELECT a1.* 
FROM WaterDepth a1 
INNER JOIN ( SELECT MAX(`date`) as `max` 
             FROM WaterDepth
             WHERE `date` &gt;= CURRENT_DATE - INTERVAL 7 DAY 
                 -- or 6 day? 7 day gives 8 different dates...
                 -- or today record not needed? if so add
                 -- AND `date` &lt; CURRENT_DATE
             GROUP BY DATE(`date`) ) a2 ON a1.`date` = a2.`max`