MySQL order by with conditional clauses

Question

I have these 2 tables

CREATE TABLE `users` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `status` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=330031656 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
CREATE TABLE `user_meta` (
 `id` int NOT NULL AUTO_INCREMENT,
 `user_id` bigint NOT NULL,
 `meta_id` bigint NOT NULL,
 `value` bigint NOT NULL,
 PRIMARY KEY (`id`),
 KEY `usermeta_user_id_meta_type_meta_value` (`user_id`,`meta_id`,`value`),
 CONSTRAINT `user_meta_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16728 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

The customer requirement is that they want to sort based on value, for a specific meta_id. For example, let's say there are 10 meta_ids, and the customer selects meta_id=1111. In this case, all the users who have meta_id=1111 associated should be sorted by their value first, and the other users who don't have meta_id=1111 can come in any order at the bottom.

There have been similar questions asked and I've tried to form queries based on their answers, but they don't seem to work for me.

This answer and this one too talk about using if-else and case-when-then statements, but when I try any of these

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by if(um.meta_id=1111, value, 1);
select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case um.meta_id
when 1111 then value else 1 end;
select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case 
when um.meta_id = 1111 then value else u.id end;

I get this

+-----------+---------+------------+
| id | meta_id | value |
+-----------+---------+------------+
| 326480529 | 200 | 1730358000 |
| 326850494 | 1111 | 1730185200 |
| 326785127 | 1111 | 1730271600 |
| 326833934 | 1111 | 1730358000 |
| 326467136 | 1111 | 1730358000 |
| 328079379 | 1111 | 1730793600 |
+-----------+---------+------------+

I want all the users with meta_id=1111 to come at the top, but neither do they come at the top, nor are they sorted within themselves. Similarly, for desc order, the ones with meta_id=1111 should come at the top, sorted in descending order, and all the others can come at the bottom, such as this

+-----------+---------+------------+
| id | meta_id | value |
+-----------+---------+------------+
| 328079379 | 1111 | 1730793600 |
| 326833934 | 1111 | 1730358000 |
| 326467136 | 1111 | 1730358000 |
| 326785127 | 1111 | 1730271600 |
| 326850494 | 1111 | 1730185200 |
| 326480529 | 200 | 1730358000 |
+-----------+---------+------------+

I'd really appreciate any help or direction in getting this right. Thank you so much!

I'm also posting some INSERT statements for both the tables to make it easier to replicate on your local machines

INSERT INTO `users` (`id`,`status`) VALUES (328079379,'active');
INSERT INTO `users` (`id`,`status`) VALUES (326833934,'active');
INSERT INTO `users` (`id`,`status`) VALUES (326467136,'deleted');
INSERT INTO `users` (`id`,`status`) VALUES (326785127,'inactive');
INSERT INTO `users` (`id`,`status`) VALUES (326850494,'removed');
INSERT INTO `users` (`id`,`status`) VALUES (326480529,'active');
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13155,328079379,1111,1730793600);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13045,326833934,1111,1730358000);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13009,326467136,1111,1730358000);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13010,326785127,1111,1730271600);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13051,326850494,1111,1730185200);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13008,326480529,200,1730358000);

• Sidharth Samant: I've added some sample INSERT statements and the expected result
• Ergest Basha: That's perfect. I have added an answer and I guess meta_id=5 is a typo and should be meta_id=1111
• Sidharth Samant: Yes, thank you for catching that. I've edited my question.

Answer 1

Let's say there are 10 meta_ids, and the customer selects meta_id=1111. In this case, all the users who have meta_id=1111 associated should be sorted by their value first, and the other users who don't have meta_id=1111 can come in any order at the bottom.

To achieve the desired order users with meta_id=1111 needs to be prioritizes by sorting them based on their value first.

Try,

SELECT u.id, 
 um.meta_id, 
 um.value
FROM users u
INNER JOIN user_meta um ON um.user_id = u.id 
ORDER BY CASE WHEN um.meta_id = 1111 THEN 1 ELSE 2 END, um.value;

See example

• Sidharth Samant: This works absolutely perfectly! Thank you so much! Years and years of using ORMs have made my SQL skills rusty.
• Rick James: More concisely: ORDER BY um.meta_id = 1111 DESC, um.value