MYSQL - Latest results when grouped by a certain field

Question

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 idUsuario and showing only showing the records where the status with the data most recent 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 that result as efficiently as possible?

Answer 1

You can do it like this:

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

I hope I’ve helped.