MySQL / Sorting and grouping data by date

Question

I'm trying to group the data to me that they just want the last record of each ID, what should I do?

In access to solve my problem this way:

SELECT Table1.id, Last(Table1.status) AS LastStatus, Last(Table1.date) AS LastDate
FROM Table1
GROUP BY Tabele1.id;

My table

 ____________________________
|  ID  | STATUS |    DATE    |
|123456|   1    | 15/04/2016 |
|123456|   2    | 16/04/2016 |
|123456|   3    | 17/04/2016 |
|123456|   1    | 18/04/2016 |
|654321|   3    | 19/04/2016 |
|654321|   4    | 20/04/2016 |
|654321|   2    | 21/04/2016 |
|98765 |   3    | 22/04/2016 |
|98765 |   1    | 23/04/2016 |
|98765 |   2    | 24/04/2016 |
|98765 |   3    | 25/04/2016 |
------------------------------

The desired result is the last record of each ID.

 ____________________________
|  ID  | STATUS |    DATE    |
|123456|   1    | 18/04/2016 |
|654321|   2    | 21/04/2016 |
|98765 |   3    | 25/04/2016 |
------------------------------

`select max(date)... group by id, status`? – Marc B May 27 '16 at 19:35 — Marc B, May 27 '16 at 19:35

score 0 · Accepted Answer · answered May 27 '16 at 19:35

0

You can use a where in with a subquery that return thx max date for the id

select * from my_table 
where (id, date)  in (  select id, max(date)  
                        from my_table 
                        group by id   )
order by date

or if you need order by status

select * from my_table 
where (id, date)  in (  select id, max(date)  
                        from my_table 
                        group by id   )
order by status

answered May 27 '16 at 19:35

ScaisEdge

131,976
10
91
107

It works, but has otherwise no subquery? (Thank you for your help). – Rodrigo Prazim May 27 '16 at 19:59
I know this way .. why you don't like subquery .. you need a subset for matching the max(date) and this is the simplest way .. – ScaisEdge May 27 '16 at 20:04
I like subqueries, the problem is that I will use enough subqueries. – Rodrigo Prazim May 27 '16 at 20:06
Another way is more complex and with a more heavy impact on performance .. in this case i think is the most convenient solution .. – ScaisEdge May 27 '16 at 20:08
Well anyway .. if my answer ir right .. is fair mark it as accepted .. – ScaisEdge May 27 '16 at 20:21
I do not have enough points to do so. I'm sorry! – Rodrigo Prazim May 27 '16 at 20:23
Mark as accepted is always possible. also with 1 point only.. you have 9 and when you accept an asnwer you obtain +2 points. Is mark as useful that require 15.. – ScaisEdge May 28 '16 at 06:12
I'm sorry, I'm new here. I have done the mark, thank you! – Rodrigo Prazim May 29 '16 at 22:00

MySQL / Sorting and grouping data by date

1 Answers1