Sample table
| id | id_sequence | date |
|---|---|---|
| 1 | 1 | 2022-06-27 |
| 2 | 1 | 2022-06-27 |
| 3 | 1 | 2022-06-27 |
| 4 | 2 | 2022-06-27 |
| 5 | 2 | 2022-06-27 |
| 6 | 1 | 2022-06-28 |
| 7 | 1 | 2022-06-28 |
| 8 | 2 | 2022-06-28 |
| 9 | 2 | 2022-06-28 |
Expected Output
| id | id_sequence | date |
|---|---|---|
| 3 | 1 | 2022-06-27 |
| 5 | 2 | 2022-06-27 |
| 7 | 1 | 2022-06-28 |
| 9 | 2 | 2022-06-28 |
how can I make a query to get latest data on every date in MySql. tried to use MAX(id) for the id_sequence but it does not return a correct value since the expected output will take only highest id of every sequence and the output will only display distinct data of id_sequence 1,2 at date 2022-06-28.
CodePudding user response：
If you want to make sure dates are taken distinctively, you need to add it inside the GROUP BY clause.
SELECT MAX(id) AS id,
id_sequence,
date_
FROM tab
GROUP BY id_sequence,
date_
ORDER BY id
Check the demo here.