How do I select last entry for today + last 6 days data

Question

How do I select the most recent last entry for today + last 6 days data for end of the day once per day only

There are many rows for the same day with different timings.

It should select 7 rows of data only.

+----+-----------------------+
| C1 |   C2                  |
+----+-----------------------+
|  1 |   2019-09-19 00:01:02 |
|  2 |   2019-09-19 00:05:02 |
|  3 |   2019-09-19 23:59:02 |
|  4 |   2019-09-20 00:15:02 |
|  5 |   2019-09-21 00:15:02 |
|  6 |   2019-09-22 00:15:02 |
|  7 |   2019-09-23 00:15:02 |
|  8 |   2019-09-24 00:15:02 |
|  9 |   2019-09-25 00:10:02 |
| 10 |   2019-09-25 00:12:02 |
+----+-----------------------+

Possible duplicate of [Select data between a date/time range](https://stackoverflow.com/questions/15821379/select-data-between-a-date-time-range) or even closer to your needs [MySQL Select last 7 days](https://stackoverflow.com/questions/24272335/mysql-select-last-7-days) — caramba, Sep 25 '19 at 05:39
Please post what you have already tried. check out (https://stackoverflow.com/help/how-to-ask) for more information on how to ask. — Praveen Gehlot, Sep 25 '19 at 05:44
@racraman thank you for pointing that out! (I didn't see that) close vote retracted — caramba, Sep 25 '19 at 05:46

Nick · Accepted Answer · 2019-09-25T06:30:21.963

2

This query will give you the results you want. It finds the top 7 maximum C2 values on a daily basis in a subquery and then JOINs that to the original table to get the values for that day:

SELECT d1.*
FROM data d1
JOIN (SELECT DATE(C2) AS Day, MAX(C2) AS C2
      FROM data
      GROUP BY Day
      ORDER BY Day DESC
      LIMIT 7) d2 ON d2.C2 = d1.C2
ORDER BY Date(d1.C2)

Output (for a slightly bigger sample in order to demonstrate only getting 7 results)

C1  C2
3   2019-09-19 23:59:02
4   2019-09-20 00:15:02
5   2019-09-21 00:15:02
6   2019-09-22 00:15:02
7   2019-09-23 00:15:02
8   2019-09-24 00:15:02
10  2019-09-25 00:12:02

Demo on dbfiddle

edited Sep 25 '19 at 06:30

answered Sep 25 '19 at 05:49

Nick

138,499
22
57
95

I can never seem to keep track of whether `LIMIT` can be used this way, I guess it can be +1. You might want to stick with the OP's original data though. – Tim Biegeleisen Sep 25 '19 at 05:55
@TimBiegeleisen thanks - the problem with OPs original data is that it only has 7 days, so it won't demonstrate that the query is correctly selecting only the latest and the prior 6 days. – Nick Sep 25 '19 at 05:56
@Strawberry yes it would... I should have done it that way to begin with. Have done so now. – Nick Sep 25 '19 at 06:31
Thank you @Nick!! and everyone as well for your help! – Jimmy Sep 26 '19 at 07:23

Tim Biegeleisen · Answer 2 · 2019-09-25T05:56:10.130

1

We can handle this using ROW_NUMBER, if you are using MySQL 8+:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(C2) ORDER BY C2 DESC) rn
    FROM yourTable
    WHERE C2 >= CURDATE() - INTERVAL 6 DAY
)

SELECT C1, C2
FROM cte
WHERE rn = 1;

Demo

edited Sep 25 '19 at 05:56

answered Sep 25 '19 at 05:49

Tim Biegeleisen

502,043
27
286
360

@Nick No...I think I should be subtracting 6 days instead. The 7th minus 6 days lands on the 1st, but for a total of 7 days inclusive. – Tim Biegeleisen Sep 25 '19 at 05:56

racraman · Answer 3 · 2019-09-25T06:33:10.240

I would use a GROUP BY to pick for each day, so :

SELECT *
FROM data
WHERE C2 IN (
     SELECT max(C2)
     FROM data
     WHERE C2 > curdate() - interval 3 day
     GROUP BY date(C2)
     )
ORDER BY C2

I always like using the “raw” values of columns in query criteria (like the “WHERE C2”), as that readily enables the database to use indexes where it decides it needs to.

Fiddle at : https://www.db-fiddle.com/f/Bh2EU5bcQvmJfyCZqExmS/1

score 0 · Answer 4 · answered Sep 25 '19 at 11:47

0

One method for doing this is:

select t.*
from t
where t.c2 = (select min(t2.c2)
              from t t2
              where t2.c2 >= date(t.c1) and
                    t2.c2 < date(t.c1) + interval 1 day
             )
order by date desc
limit 7

answered Sep 25 '19 at 11:47

Gordon Linoff

1,242,037
58
646
786

How do I select last entry for today + last 6 days data

4 Answers4

Demo