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