SQL query order by pattern

Question

I am using this particular example

https://www.w3schools.com/sql/sql_orderby.asp

I want to get the list of customers ordered by country pattern.

For instance, Country starting with As should be first in number, followed by countries starting with Ge, followed by country starting with Fr.

If I need to order by using 1 pattern search in the country I can achieve using the following code :

SELECT * FROM Customers
ORDER BY (CASE WHEN Country LIKE 'Fr%' THEN 0
ELSE 1
END)ASC;

How can I add 2 more conditions to the ORDER BY clause ? Thank you.

Answer 1

You can add multiple CASE for your additional "conditions" by incrementing the number each time:

SELECT * FROM Customers
ORDER BY (CASE
    WHEN Country LIKE 'As%' THEN 0
    WHEN Country LIKE 'Ge%' THEN 1
    WHEN Country LIKE 'Fr%' THEN 2
    ELSE 3
END) ASC;