Mysql query to get user's specific status record based on it's time priority ( if specific status processed last/ recent)

Question

I have user_status table it consist of different users and their status( A,B,C,D) now my requirement is status A should get compare with all other status(B,C,D) and get only record of status A value if status A updated time is greater then all other status but status A is processed before (B,C,D) so updated time is less compare to other status then in this scenario don't get any record

id  user_id  status         updated

1       1       C       2023-12-28 23:32:06
2       1       B       2023-12-28 23:34:06
3       1       A       2023-12-28 23:36:06

4       2       B       2023-12-27 23:12:02   
5       2       D       2023-12-27 23:15:08
6       2       A       2023-12-27 23:18:06
7       2       C       2023-12-27 23:22:06

Expected output : id=3 For user-1 record should get=> for this user recent status is A and B,C,D processed before A so priority is given to Status A

For user-2 no record should get because status A must processed last however it got already processed and hence last status for this user is C

Mysql query :

SELECT t1.* 
FROM table t1
INNER JOIN table t2 on t2.status=t1.status where t1.status='A' and t1.updated > t2.updated 

Answer 1

Based on my reading of your question, it seems that for each user_id value you want to compare the updated value for the row with status = 'A' with the updated value for the rows with status <> A and select the A row if its updated value is greater than the updated value for the other rows. If so, then the following should work.

Schema (MySQL v5.7)

CREATE TABLE user_status (
  id INT NOT NULL PRIMARY KEY,
  user_id INT NOT NULL,
  status CHAR(1) NOT NULL,
  updated DATETIME NOT NULL
  );
  
INSERT INTO user_status VALUES
  (1, 1, 'C', '2023-12-28 23:32:06'),
  (2, 1, 'B', '2023-12-28 23:34:06'),
  (3, 1, 'A', '2023-12-28 23:36:06'),
  (4, 2, 'B', '2023-12-27 23:12:02'),
  (5, 2, 'D', '2023-12-27 23:15:08'),
  (6, 2, 'A', '2023-12-27 23:18:06'),
  (7, 2, 'C', '2023-12-27 23:22:06')
;

---

Query #1

SELECT u.* FROM user_status u JOIN (
    SELECT user_id, MAX(updated) AS max_updated FROM user_status
    WHERE status <> 'A'
    GROUP BY user_id
) sq ON u.user_id = sq.user_id AND u.status = 'A' AND u.updated > sq.max_updated
;

id	user_id	status	updated
3	1	A	2023-12-28 23:36:06

---

View on DB Fiddle

Note

The join condition ... AND u.status = 'A' ... is not really necessary since given that u.updated > sq.max_updated, then the status column value must be 'A'. So we really only need:

SELECT u.* FROM user_status u JOIN (
    SELECT user_id, MAX(updated) AS max_updated FROM user_status
    WHERE status <> 'A'
    GROUP BY user_id
) sq ON u.user_id = sq.user_id AND u.updated > sq.max_updated
;

But if we changed the condition u.updated > sq.max_updated to u.updated >= sq.max_updated, then we would have to add the additional test u.status = 'A' because there will always be at least one non-'A' row for which u.updated >= sq.max_updated.

Further Explantion

Consider the following subquery (you can refer to the db fiddle, which I have updated to show what the subquery yields):

SELECT user_id, MAX(updated) AS max_updated FROM user_status
WHERE status <> 'A'
GROUP BY user_id

This creates one row for each unique user_id with two columns, the user_id and max_updated, which is the greatest value of the updated column for that user_id considering only rows whose user_status is not 'A':

user_id	max_updated
1	2023-12-28 23:34:06
2	2023-12-27 23:22:06

Then for a given user_id if there is a corresponding user_status row with status = 'A' that has an updated column greater than this subquery's max_updated value, then we then wish to select that row. We can determine such rows by joining the user_status table with the above subquery using the appropriate join conditions. Note that this is not a self join, which is when you join a table with itself. In this case we are joining the user_status table with a subquery that is based on the user_status table but which is not the user_status table itself.