SQL Server - Order by case

Question

I am trying to get the following query to display the results in alphabetical order by City except for the fact that I want "Berlin" to appear at the top of the list

So the results would look something like

Berlin
Algeria
Australia
Fiji
Greece
...

Hope that makes sense,

I currently have the following...

SELECT CompanyName, City
FROM customers
ORDER BY case when City = 'Berlin' END

score 16 · Answer 1 · answered Sep 08 '10 at 10:46

16

Almost:

SELECT CompanyName, City
FROM customers
ORDER BY CASE WHEN City = 'Berlin' THEN 0 ELSE 1 END, City

answered Sep 08 '10 at 10:46

tdammers

20,353
1
39
56

Sounds like this should work, but why doesn't it on this SQL tryit page?? – Tom Sep 08 '10 at 11:04

Ralf de Kleine · Answer 2 · 2010-09-09T11:42:00.983

7

SELECT CompanyName, City, CASE WHEN City = 'Berlin' THEN 0 ELSE 1 END AS Ordering
FROM customers
ORDER BY Ordering, City

edited Sep 09 '10 at 11:42

answered Sep 08 '10 at 10:47

Ralf de Kleine

11,464
5
45
87

1

+1 for SQL-92 compliance i.e. only column names from the `SELECT` clause should be used in the `ORDER BY` clause. I've chosen to turn a blind eye to the missing `AS` keyword i.e. `AS Ordering` ;) – onedaywhen Sep 09 '10 at 09:17

score 2 · Answer 3 · answered Sep 08 '10 at 10:46

2

Try something like

SELECT CompanyName, City 
FROM customers 
ORDER BY case when City = 'Berlin' THEN 0 ELSE 1 END, City

answered Sep 08 '10 at 10:46

Adriaan Stander

162,879
31
289
284

score 1 · Answer 4 · answered Sep 08 '10 at 10:45

1

SELECT CompanyName, City 
FROM customers 
ORDER BY case when City = 'Berlin' then 1 else 999 END asc

answered Sep 08 '10 at 10:45

Kirill Leontev

10,641
7
43
49

1

Please forgive my ignorance but what does this bit mean "then 1 else 999"?? – Tom Sep 08 '10 at 10:47
1

The data resulting from the Case. When its Berlin it returns 0 else it returns 999 – Ralf de Kleine Sep 08 '10 at 10:48
do this query need customers as well in order by clause? Without that its not giving results for numeric columns – Anil Soman Sep 08 '10 at 10:58

badbod99 · Answer 5 · 2010-09-08T10:59:54.497

As blank string appears first in any string ordered list, all other results sorted normally. So this works perfectly:

SELECT CompanyName, City
FROM customers
ORDER BY CASE WHEN City = 'Berlin' THEN '' ELSE City END

Tested with:

CREATE TABLE customers (CompanyName VARCHAR(50), City VARCHAR(50))

INSERT INTO customers VALUES ('Customer1', 'Berlin')
INSERT INTO customers VALUES ('Customer2', 'Algeria')
INSERT INTO customers VALUES ('Customer3', 'Australia')
INSERT INTO customers VALUES ('Customer4', 'Fiji')
INSERT INTO customers VALUES ('Customer5', 'Greece')

SELECT CompanyName, City
FROM customers
ORDER BY CASE WHEN City = 'Berlin' THEN '' ELSE City END

-- OUPUT
-- Customer1    Berlin
-- Customer2    Algeria
-- Customer3    Australia
-- Customer4    Fiji
-- Customer5    Greece

SPIRiT_1984 · Answer 6 · 2010-09-08T11:55:44.363

0

How about using Union? Something like this, for example:

SELECT 1 as Weight, CompanyName, City FROM customers
WHERE city='Berlin'
UNION ALL
SELECT 2 as Weight, CompanyName, City FROM
customers 
WHERE city<>'Berlin'
ORDER BY Weight, City

edited Sep 08 '10 at 11:55

answered Sep 08 '10 at 11:21

SPIRiT_1984

2,717
3
29
46

2

That doesn't work; ORDER BY applies to the entire union, and you'll still be left with Algeria up top. – LittleBobbyTables - Au Revoir Sep 08 '10 at 11:29
Thanks for the remark. But now with parenthesis it works correctly. I used the SQL tryit page for checking – SPIRiT_1984 Sep 08 '10 at 11:43
Sorry, no, it's wrong, my mistake. The Union all ignores the ordering actually... – SPIRiT_1984 Sep 08 '10 at 11:46
Now it really works correctly, based on the idea of weighted sorting – SPIRiT_1984 Sep 08 '10 at 11:56

SQL Server - Order by case

6 Answers6

Linked