Back to articles list Articles Cookbook
6 minutes read

How to Use CASE in ORDER BY in SQL

This article will show you how and when to use CASE in an ORDER BY clause.

Have you ever used a CASE statement? I’m sure you have, at least in a SELECT statement. But have you ever used it in an ORDER BY clause? No? You will, once I show you how!

Don’t worry if you’ve never used a CASE statement. I’ll show and explain it to you with a short example. Then I’ll move to other uses of the CASE statement, especially in an ORDER BY clause.

If you want multiple knowledge sources, here’s an article explaining what CASE is. And here’s another one explaining how you can use CASE to add logic to a SELECT.

What Is a CASE Statement?

To put it very simply, it’s an SQL statement that goes through and returns values according to the conditions specified. It is SQL’s way of writing the IF-THEN-ELSE logic and consists of five keywords: CASE, WHEN, THEN, ELSE, and END.

When used in a SELECT statement, it works like this: if it is the case when the condition is met, then return a certain value, or else return some other value, and end checking the conditions. The syntax looks like this:

CASE
 WHEN  THEN ,
 WHEN  THEN 
 ELSE 
END AS 

I think this syntax is best explained by how it works in an example. The most basic use of CASE is in a SELECT statement, so let’s start with an example.

CASE in SELECT

Here’s the table films I’ll use in this example:

idfilm_titleyeardirector
1True Grit2010The Coen Brothers
2Da 5 Bloods2020Spike Lee
3Alien1979Ridley Scott
4The Bridges Of Madison County1995Clint Eastwood
5Get Out2017Jordan Peele
6Annie Hall1977Woody Allen
7Goodfellas1990Martin Scorsese
8Dr.Strangelove1964Stanley Kubrick
9You Were Never Really Here2017Lynne Ramsay
10Albert Nobbs2011Rodrigo Garcia

Now, say we want to write a query that, along with the columns from the table, shows an additional column with the century in which the title was filmed. An example query looks like this:

SELECT
*,
	CASE
		WHEN year < 2001 THEN '20th-century film'
		ELSE '21st-century film'
	END AS century
FROM films;

This query selects all the columns from the table films. Then it uses a CASE statement to put values in a new column called century. This statement means: when the column year is below (i.e., older than) 2001, then the value in the column century should be '20th-century film'. If it’s not, then the value should be '21st-century film'.

Let’s see the results of the query:

idfilm_titleyeardirectorcentury
1True Grit2010The Coen Brothers21st-century film
2Da 5 Bloods2020Spike Lee21st-century film
3Alien1979Ridley Scott20th-century film
4The Bridges Of Madison County1995Clint Eastwood20th-century film
5Get Out2017Jordan Peele21st-century film
6Annie Hall1977Woody Allen20th-century film
7Goodfellas1990Martin Scorsese20th-century film
8Dr.Strangelove1964Stanley Kubrick20th-century film
9You Were Never Really Here2017Lynne Ramsay21st-century film
10Albert Nobbs2011Rodrigo Garcia21st-century film

Is CASE Used Only in SELECT Statements?

Nope! Although it is most often used there, CASE is not limited to SELECT statements. For example, you can use it in clauses like IN, WHERE, HAVING, and ORDER BY.

Using a CASE statement in a query once doesn’t mean you have hit your quota for using it. You can use it multiple times in a single query. Or you can use it just once. This means you don’t need to have CASE in SELECT if you just want to use it in an ORDER BY.

Speaking of which, the main point of this article is to show you how the CASE statement works in an ORDER BY clause. I’m getting there now.

When Is a CASE Statement Used in an ORDER BY?

The ORDER BY clause is used to sort the result in either ascending or descending order. Want a refresher on how it works? No problem! This article explains what an ORDER BY does.

Introducing a CASE statement here can enhance your ORDER BY by allowing you to order results by some other (and multiple) criteria. For example, you can use it when you want to sort string values according to some criteria other than alphabetical order, such as by the hierarchical positions of job titles in a company.

Using a CASE statement also allows you to sort data according to multiple criteria. For example, you may want to sort your users by country and name, but by country and state instead if they are from the U.S.

I’ll show you a practical example, and you’ll immediately know what I’m talking about.

CASE in ORDER BY

Here, we’ll be working with the table shops, which contains the following data:

idshop_namecountrystatecity
1Zoltan's shopCroatiaNULLZagreb
2Ante PortasCroatiaNULLRijeka
3Green Mile 1USATennesseeMemphis
4Jan Pieter's PitaCroatiaNULLSplit
5Green Mile 2USATennesseeChattanooga
6Green Mile 3USANew OrleansLouisiana
7Krystyna's shopHungaryNULLPecs
8Quinn & SandyHungaryNULLGyor
9Green Mile 4USACaliforniaSan Francisco
10FragolaCroatiaNULLOsijek

We have to order the data by country first. Then, every shop within the same country should be sorted by city. If the shop is in the U.S., we need to sort it next by the column state. The code that solves this little problem is:

SELECT *
FROM shops
ORDER BY country,
	CASE
		WHEN country = 'USA' THEN state
		ELSE city
	END;

It selects all the columns from the table shops. It then orders the result first by country, then by the criteria in the CASE statement. It says if country = 'USA', then the result is sorted by state. If it’s not, then it is sorted by city.

It looks like this code indeed returns the desired result:

idshop_namecountrystatecity
10FragolaCroatiaNULLOsijek
2Ante PortasCroatiaNULLRijeka
4Jan Pieter's PitaCroatiaNULLSplit
1Zoltan's shopCroatiaNULLZagreb
8Quinn & SandyHungaryNULLGyor
7Krystyna's shopHungaryNULLPecs
9Green Mile 4USACaliforniaSan Francisco
6Green Mile 3USANew OrleansLouisiana
5Green Mile 2USATennesseeChattanooga
3Green Mile 1USATennesseeMemphis

Other Uses of the CASE Statement

You can also use the CASE statement with keywords other than the ones I have mentioned. For example, you can use it with DESC. Let’s say I put it in the above code:

SELECT *
FROM shops
ORDER BY country,
	CASE
		WHEN country = 'USA' THEN state
		ELSE city
	END DESC;

In the previous example, I’ve omitted the DESC keyword. The result is sorted in ascending order by default. By writing DESC after the CASE statement, you get the shops within the countries ordered by city in descending order, not ascending as in the previous case. U.S. shops are ordered by state but this time in descending order.

We can see the result here:

idshop_namecountrystatecity
1Zoltan's shopCroatiaNULLZagreb
4Jan Pieter's PitaCroatiaNULLSplit
2Ante PortasCroatiaNULLRijeka
10FragolaCroatiaNULLOsijek
7Krystyna's shopHungaryNULLPecs
8Quinn & SandyHungaryNULLGyor
5Green Mile 2USATennesseeChattanooga
3Green Mile 1USATennesseeMemphis
6Green Mile 3USANew OrleansLouisiana
9Green Mile 4USACaliforniaSan Francisco

You can also use CASE statements with UPDATE, INSERT, and DELETE. These are called data modifying statements. Here’s an article explaining how to use CASE with those statements. You can also use it for conditional summarization.

Want to Learn More About the CASE Statement?

It probably comes to you as a surprise that CASE statements aren’t used only in SELECT statements. You’ve seen it come in handy when used with an ORDER BY. It opens up the possibilities for ordering query results.

That’s not the end. The CASE statement can be used with some other statements and keywords, such as data modifying statements, or even DESC, IN, WHERE, and HAVING.

Now it’s time to learn more and practice what you learn. You get all that with our Standard SQL Functions course. This course is a part of a much wider track named SQL from A to Z. What are you waiting for?