The marketing department wants to focus on the customers from Noth America first. Get the ID, last name and country of all customers. Build the list by bringing up the customers living in Canada and in the USA first, and finally order them by ID. Tip: use the IN expression in the ORDER BY clause.
I've tried many times
SELECT CustomerID, LastName, Country
FROM Customer
ORDER BY Country IN ('Canada', 'USA'), CustomerID
but it doesn't seem to work, as it takes the specified fields from all customers in the order they appear in the original table (which is also ordered by ID), even if I remove CustomerID from the ORDER BY clause, whithout caring to which country they belong to.
What should I do? I'm really new to SQL, and have no idea on how to fix this.
Edit: WHERE ins't suitable at all, as I need to take in consideration all customers, only making sure the Canadian and American ones appear at the top of the list.
Also I'm unsure statements like UNION, AS, EXCEPT and things like that are meant to be used, because the tutorial didn't go that deep already.
Not every DBMS has a boolean datatype. So the result of
Country IN ('Canada', 'USA'),
which is a boolean, can not be sorted in these DBMS.
You can use a CASE expression, however, to assign a value:
SELECT CustomerID, LastName, Country
FROM Customer
ORDER BY CASE WHEN Country IN ('Canada', 'USA') THEN 1 ELSE 2 END, CustomerID;