0

I have the following tables on my MYSQL database:

USERS

ID | login
---------------
1  | user1
2  | user2
3  | user3

STATUS

ID | userID    | date                | 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 query the STATUS table to get only the rows with the most recent date and where the column status is equal to a with no repeated userID?

These are the lines that I want my query to return in this case:

userID    | date                | status
2         | 2018-05-15 10:00:00 | a
3         | 2018-05-25 10:00:00 | a
Bruno Costa
  • 7
  • 1
  • 3

2 Answers2

0

you could try the below:

select s1.userID, s1.date, s1.status from status s1
inner join (select userID, max(date) date from status group by userID) s2 
  on s2.date = s1.date and s1.userID = s2.userID
where s1.status = 'a';

Above I create a table that has the max(date) per UserID. We can join this table back to the raw table, using inner join we ensure results are only latest date rows. We use a where clause to filter the output for only those rows with Status = 'a'

You can play with the query in SQL Fiddle here

EoinS
  • 5,405
  • 1
  • 19
  • 32
  • Thanks for answering. This query you suggested will also bring as a result the row with the userID equals to 1 and I don't want that since I've changed its status to d. – Bruno Costa Jun 15 '18 at 10:13
  • @BrunoCosta thanks for clarifying. Please see the updated answer – EoinS Jun 15 '18 at 14:07
0

You can ma nage as you want INTEVRAL n DAY regarding how many days back you wanna check.

SELECT DISTINCT * FROM STATUS WHERE STATUS.date = DATE_SUB(CURDATE(), INTERVAL 10 DAY)
mooga
  • 3,136
  • 4
  • 23
  • 38