PROPOSED QUERY
SELECT B.* FROM
(SELECT user_id,MAX(enabled_date) maxdate FROM mytable GROUP BY user_id) A
INNER JOIN mytable B ON A.user_id=B.user_id AND A.maxdate=B.enabled_date
ORDER BY B.user_id;
What you need is a query that gets the maximum enabled_date
for each user_id
and then join that result back to the same table.
YOUR SAMPLE DATA LOADED
USE ashishthakur
CREATE TABLE mytable
(
user_id INT,
is_service_enabled VARCHAR(10),
enabled_date DATE
);
INSERT INTO mytable (user_id,is_service_enabled,enabled_date) VALUES
(101,'FALSE','2022-02-12'),
(102,'TRUE' ,'2021-06-07'),
(101,'TRUE' ,'2022-04-12'),
(100,'TRUE' ,'2021-07-10'),
(100,'FALSE','2021-09-10'),
(104,'FALSE','2022-09-07'),
(100,'TRUE' ,'2022-09-18');
SELECT * FROM mytable;
Database changed
mysql> CREATE TABLE mytable
-> (
-> user_id INT,
-> is_service_enabled VARCHAR(10),
-> enabled_date DATE
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO mytable (user_id,is_service_enabled,enabled_date) VALUES
-> (101,'FALSE','2022-02-12'),
-> (102,'TRUE' ,'2021-06-07'),
-> (101,'TRUE' ,'2022-04-12'),
-> (100,'TRUE' ,'2021-07-10'),
-> (100,'FALSE','2021-09-10'),
-> (104,'FALSE','2022-09-07'),
-> (100,'TRUE' ,'2022-09-18');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM mytable;
+---------+--------------------+--------------+
| user_id | is_service_enabled | enabled_date |
+---------+--------------------+--------------+
| 101 | FALSE | 2022-02-12 |
| 102 | TRUE | 2021-06-07 |
| 101 | TRUE | 2022-04-12 |
| 100 | TRUE | 2021-07-10 |
| 100 | FALSE | 2021-09-10 |
| 104 | FALSE | 2022-09-07 |
| 100 | TRUE | 2022-09-18 |
+---------+--------------------+--------------+
7 rows in set (0.00 sec)
mysql>
PROPOSED QUERY EXECUTED
mysql> SELECT B.* FROM
-> (SELECT user_id,MAX(enabled_date) maxdate FROM mytable GROUP BY user_id) A
-> INNER JOIN mytable B ON A.user_id=B.user_id AND A.maxdate=B.enabled_date
-> ORDER BY B.user_id;
+---------+--------------------+--------------+
| user_id | is_service_enabled | enabled_date |
+---------+--------------------+--------------+
| 100 | TRUE | 2022-09-18 |
| 101 | TRUE | 2022-04-12 |
| 102 | TRUE | 2021-06-07 |
| 104 | FALSE | 2022-09-07 |
+---------+--------------------+--------------+
4 rows in set (0.00 sec)
mysql>
WHERE GROUP_CONCAT(is_service_enabled ORDER BY enabled_date) = 'TRUE,FALSE,TRUE'