MYSQL - Latest results when grouping by a certain field

Navigation
1

I have the following tables in my MYSQL database

USERS Table 

id | login
---------------
1  | usuario1
2  | usuario2
3  | usuario3

STATUS Table 

id | idUsuario | data                | status
1  | 1         | 2018-05-10 10:00:00 | a
2  | 2         | 2018-05-15 10:00:00 | a
3  | 3         | 2018-05-20 10:00:00 | a
4  | 3         | 2018-05-20 11:00:00 | d
5  | 1         | 2018-05-15 11:00:00 | d
6  | 3         | 2018-05-25 10:00:00 | a

How do I search the STATUS table by grouping by idUsuario and showing only showing only the records where the status with the most recent data is equal to a

This is the result I want to achieve with this query: 

idUsuario | data                | status
2         | 2018-05-15 10:00:00 | a
3         | 2018-05-25 10:00:00 | a

Any idea how I get to this result as efficiently as possible?

    
asked by anonymous 07.06.2018 / 21:58

1 answer

0

You can do this: 

SELECT idUsuario, MAX(data), status FROM STATUS
WHERE status = 'a'
GROUP BY idUsuario;

I hope I have helped.

    
07.06.2018 / 22:07
Ajax Script does not work Problems loading a DataTable in Page Load with JavaScript