How do I select data going back a period of time for each group?
I want for each group id
to get the latest week's worth of data. Not from a specific date, but counting backwards from the MAX(startTime)
of each individual group.
However, the following does not seem to work. I assume it's because startTime
in each group (a single value) is BETWEEN
ed by itself? Otherwise, how do I keep it in my filter?
SELECT
id
, startTime
FROM MyTable
GROUP BY id, startTime
HAVING startTime BETWEEN MAX(startTime) - INTERVAL 1 WEEK AND MAX(startTime)
What's the right query?
Also, in my case it has to work with MySQL 5.7.
SELECT m.id, m.startTime
FROM (
SELECT id, MAX(startTime) AS startTime
FROM MyTable GROUP BY id
) AS x
JOIN MyTable AS m ON m.id = x.id
AND m.startTime BETWEEN x.startTime - INTERVAL 1 WEEK AND x.startTime;