ORDER BY + IN statement?

Question

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.

When asking SQL questions, always tag the DBMS used. SQL dialects differ, and what is possible in one DBMS may need another solution in another. — Thorsten Kettner, Jun 15 '16 at 09:59

score 5 · Accepted Answer · answered Jun 15 '16 at 09:55

5

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;

answered Jun 15 '16 at 09:55

Thorsten Kettner

89,309
7
49
73

This actually produced the desired result. Thanks! – user6245072 Jun 15 '16 at 11:00

score 0 · Answer 2 · answered Jul 20 '18 at 01:41

0

SELECT CustomerID, LastName, Country FROM Customer ORDER BY Country IN ('Canada', 'USA') desc, CustomerID asc

answered Jul 20 '18 at 01:41

DNU

1

Try formatting your query as code by adding four spaces before it. – Grant Miller Jul 20 '18 at 02:00

ThiepLV · Answer 3 · 2016-06-15T09:43:48.263

-1

IN expression don't return value so you can't sort

You can try:

SELECT CustomerID, LastName, Country
FROM Customer
WHERE Country＝'Canada'

UNION ALL

SELECT CustomerID, LastName, Country
FROM Customer
WHERE Country＝'USA'
ORDER BY CustomerID

Using ORDER BY with UNION, EXCEPT, and INTERSECT When a query uses the UNION, EXCEPT, or INTERSECT operators, the ORDER BY clause must be specified at the end of the statement and the results of the combined queries are sorted. The following example returns all products that are red or yellow and sorts this combined list by the column ListPrice. https://msdn.microsoft.com/en-us/library/ms188385.aspx#Union

edited Jun 15 '16 at 09:43

answered Jun 15 '16 at 09:29

ThiepLV

1,219
3
10
21

The query looks correct and should not produce any error. However, the results are ordered by CustomerID only, which is not desired. And the WHERE clauses limit the results to USA and Canada, which is also not desired. And the same could be got with a mere `Country IN ('USA', 'Canada'). Using `UNION ALL` instead makes no sense. – Thorsten Kettner Jun 15 '16 at 10:03

ORDER BY + IN statement?

3 Answers3