CODEWITHSUNDEEP
mysqlsql
Jimmy
Jimmy

Reputation: 61

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

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

Upvotes: 0

Views: 534

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 0

racraman
racraman

Reputation: 5034

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

Upvotes: 0

Nick
Nick

Reputation: 147166

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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;

enter image description here

Demo

Upvotes: 1

Related Questions