MySQL – Query to Find Last Entry for Each of the Last 7 Days

greatest-n-per-groupMySQLselectt-sql

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 >= 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

Best Answer

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

Related Solutions

Mysql – How to select the latest record of each hour in a day

That's a groupwise maximum.
You may plump for a null-self-join:

SELECT  ir1.*
FROM inverter_readings IR1
LEFT JOIN inverter_readings IR2 ON
DATE(IR1.READING_ON) = DATE(IR2.READING_ON) AND
HOUR(IR1.READING_ON) = HOUR(IR2.READING_ON) AND
IR1.READING_ON  < IR2.READING_ON
WHERE IR2.id IS NULL

sqlfiddle demo.

MySQL – How to Select Max(Date) from Union Query

So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved.

Here is the query:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
 SELECT 
    u.user_name, 
    u.first_name as First_Name, 
    u.last_name as Last_Name, 
    date_format(l.successdate, '%Y-%m-%d') as Last_Login 
  FROM confluence.logininfo l 
  JOIN confluence.user_mapping m ON m.user_key = l.username 
  JOIN confluence.cwd_user u ON m.username = u.user_name 
  JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T' 
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc

Best Answer

Related Solutions

Mysql – How to select the latest record of each hour in a day

MySQL – How to Select Max(Date) from Union Query

Related Question