SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `test_table` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `current_status` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci', `dateadd` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, `user_id` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ); INSERT INTO `test_table` VALUES ( 1, 'new', '2020-10-01 03:00:59', 3 ), ( 2, 'old', '2020-10-11 02:00:59', 3 ), ( 3, 'new', '2020-10-11 05:00:59', 3 ), ( 4, 'new', '2020-10-12 07:00:59', 3 ), ( 5, 'old', '2020-11-01 03:00:59', 3 ), ( 6, 'new', '2020-11-12 03:00:59', 3 ), ( 7, 'new', '2020-11-14 03:00:59', 3 ), ( 8, 'old', '2020-11-15 03:00:59', 3 ); SELECT * FROM `test_table`; SELECT user_id, MIN(current_status) current_status, MIN(dateadd) dateadd, status_changed FROM ( SELECT tt.*, MIN(tt1.dateadd) as status_changed FROM `test_table` tt LEFT JOIN `test_table` tt1 ON tt1.user_id = tt.user_id AND tt1.dateadd > tt.dateadd AND tt1.current_status <> tt.current_status GROUP BY tt.id, tt.current_status, tt.user_id ) aggregated GROUP BY user_id, status_changed ORDER BY dateadd;