SQL：首先选择并排序与特定ID和特定类型匹配的行

Question

I have the following table:

id name type
-- ---- -----
1  Joe  H
1  Zoe  F
2  Ken  F
2  Ben  H
3  Lime H
4  Fine F
5  Lima F
5  Sima H

I want to select first the rows that match:

id: 5 & type: H

id: 2 & type: F

id: 3 & type: H

And after them the rest ordered by id ASC.

So the final result should be:

id name type
-- ---- -----
5  Sima H
2  Ken  F
3  Lime H
1  Joe  H
1  Zoe  F
2  Ben  H
4  Fine F
5  Lima F

I just couldn't find how to show first specific rows sorted by more than one value. I use PHP and MySqli if that helps.

Thanks

Answer 1

You can order with a case statement:

select * from table
order by
    case 
        when id = 5 and type = 'H' then 0
        when id = 2 and type = 'F' then 1
        when id = 3 and type = 'H' then 2
        else 3
    end, id, name