MYSQL - Latest results when grouped by a certain field

Asked

Viewed 114 times

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

mysql group-by

1 answer

0

You can do it like this:

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

I hope I’ve helped.

  • Thanks for trying to help... I’ve tried something similar and it didn’t work because in this case, as you’re saying in WHERE you want records with status = 'a' it will show the id record 1 in the status table when it shouldn’t pick up any records with idUsuario = 1, where I disabled the user 1 five days and an hour after it was activated.

Browser other questions tagged mysql group-by

You are not signed in. Login or sign up in order to post.