Mysql order by specific IDs first but still alphabetically-CodePudding

Is there a way I can get specific IDs at the top, but sorted alphabetically by title, followed by the rest of the records also sorted alphabetically by title?

What I have right now:

SELECT * FROM table ORDER BY FIELD(ID,2,3) DESC, title ASC

Example:

1 Lisa
2 Hannah
3 Adam
4 Brian

Should be sorted

3 Adam (because Adam is before Hannah)
2 Hannah
4 Brian (because Brian is before Lisa)
1 Lisa

CodePudding user response：

select * from table
order by 
case when id in (2,3) 
then 0 
else 1 end asc, 
title asc

CodePudding user response：

You would need a use a CASE statement that generated different values for each group that you wanted and then order by the case statement and then by the name

CodePudding user response：

From you smal sample data you can use a CASE WHEN and ORDER which fields should be at the start, the second order is name in my case

CREATE TABLE tab1
    (`id` int, `name` varchar(6))
;
    
INSERT INTO tab1
    (`id`, `name`)
VALUES
    (1, 'Lisa'),
    (2, 'Hannah'),
    (3, 'Adam'),
    (4, 'Brian')
;

SELECT * FROM tab1 ORDER BY 
CASE WHEN FIELD(ID,2,3) THEN 1 ELSE 2  END  ASC,name ASC
#, title ASC

id | name  
-: | :-----
 3 | Adam  
 2 | Hannah
 4 | Brian 
 1 | Lisa

db<>fiddle here