MySQL Order by show specific data at the top-CodePudding

I have a below data in my activity table. I want to show those records at the top whose followup_date is today onwards in ascending order, after the those records who followup_date is past date in ascending order and after that those records whose followup_date is null.

DROP TABLE IF EXISTS `activity`;
CREATE TABLE IF NOT EXISTS `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL,
  `followup_date` date DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `activity`
--

INSERT INTO `activity` (`id`, `type_id`, `followup_date`, `created`) VALUES
(1, 1, '2022-03-22', '2022-03-24 18:51:23'),
(2, 1, '2022-03-23', '2022-03-24 18:51:23'),
(3, 1, '2022-03-24', '2022-03-24 18:51:58'),
(4, 1, '2022-03-25', '2022-03-24 18:51:58'),
(5, 1, '2022-03-26', '2022-03-24 18:52:21'),
(6, 1, '2022-03-13', '2022-03-24 18:52:21'),
(7, 1, NULL, '2022-03-24 18:54:15'),
(8, 1, NULL, '2022-03-24 18:54:15');

I tried using below query but could not understand how would i use ORDER BY CASE statement to get the result mentioned below.

SELECT * FROM `activity` ORDER BY CASE WHEN followup_date IS NULL THEN 2 WHEN followup_date >= '2022-03-24' THEN 1 END ASC

Current Output:

Expected Output

What changes i will need to make in above query to get Expected Output

CodePudding user response：

I moved the expression into the select-list so we could see it in the result, but you may keep it in the ORDER BY clause:

SELECT CASE WHEN followup_date IS NULL THEN 2 
            WHEN followup_date < '2022-03-24' THEN 1 
            ELSE 0 END AS sort_bucket, 
  id, followup_date 
FROM `activity` 
ORDER BY sort_bucket ASC, followup_date ASC

Output:

 ------------- ---- --------------- 
| sort_bucket | id | followup_date |
 ------------- ---- --------------- 
|           0 |  3 | 2022-03-24    |
|           0 |  4 | 2022-03-25    |
|           0 |  5 | 2022-03-26    |
|           1 |  6 | 2022-03-13    |
|           1 |  1 | 2022-03-22    |
|           1 |  2 | 2022-03-23    |
|           2 |  7 | NULL          |
|           2 |  8 | NULL          |
 ------------- ---- ---------------