How to Sort Values in a Specific Order in SQL

Sometimes you will be required to order your data in a particular way, and often the use of typical sort modifiers like ASC and DESC are not enough. This article will show you how to use the CASE statement to order your data exactly how you need it to be.

Have you ever needed to order your data in a very specific way? One of the best methods for handling complex sorting is using CASE. The CASE statement is quite useful, as it lets you add if-else logic to your queries. We’ll use this logic SQL ORDER BY specific values. You can learn more about the CASE statement in our interactive Standard SQL Functions course. It'll teach you how to use CASE expression in SQL queries and much more.

We’ll demonstrate this SQL custom order by running through 3 distinct examples, involving different data types like integers, text values, and dates.

For our examples, we will use the orders table. This table contains information about the product orders our company has received.

orders

Custom Sort based on Integer column

We’ve received word from our manager on how they want this data to be ordered. Our result must adhere to the following rules:

• First, show the orders where between 10 and 20 items were ordered.
• Then, show the orders where less than 10 items were ordered.
• Finally, show the orders where over 20 items were ordered.

The requirements are pretty clear, let’s get to work! After some time, we’ve produced the following SQL query:

SELECT id, count
FROM orders
ORDER BY CASE WHEN count BETWEEN 10 AND 20 THEN 1
              WHEN count < 10 THEN 2
              ELSE 3END

Let’s break this down.

We select the id and count of orders to be displayed. After this, we get straight into the order of our results by using the ORDER BY clause.

You can use CASE to specify a sort key in the ORDER BY clause, which is exactly what we’re doing here. We give the first case a sorting key value of 1:

CASE WHEN count BETWEEN 10 AND 20 THEN 1

We give the second case a sorting key value of 2:

WHEN count < 10 THEN 2

This ensures that the results of the first case will be shown ahead of our second case. The last data we want to be displayed is where the order count is above 20. We can assign them a sorting key value of 3.:

ELSE 3

Alright, let’s execute our query! This results in the following records being returned:

It worked! Items with an order count of between 10 and 20 are shown first. Next come the orders where 10 items were ordered. And finally, order counts of above 20 are shown.

This is quite an advanced use of CASE. You can also use the CASE statement with data modifying commands like INSERT and UPDATE. Check out this blog post on the topic if you’re interested in learning how.

Custom Sort based on a text column

Let’s look at another example, except this time we’ll use a text column to specify the order our data should appear in.

We just got word from our boss that we need to display some of the data from our orders table and sort it based on the location the order took place in.

They want to see the orders for New York first, followed by the orders that took place in Boston, and then the orders in Toronto. The rest of the orders data must also be displayed but the order does not matter for the other locations. Let’s get to work.

We’ve arrived at the following query:

SELECT id, location, count, order_date
FROM orders
ORDER BY CASE WHEN location = 'New York' THEN 1
              WHEN location = 'Boston' THEN 2
              WHEN location = 'Toronto' THEN 3
              ELSE 4 END

This time we’re selecting all the columns. The use of ORDER BY and CASE is similar to our previous example.If you want to learn more about this topic, check out this article that covers SQL data types extensively.

Note the order we have used in our CASE WHEN expression matches our requirements exactly, showing New York, Boston, then Toronto. Let’s execute this query and see if we are successful.

Success! This is exactly how we want our data to be ordered. We can now move onto our final example. So far we have worked with both number and text data types.

Custom Sort based on Date column

For our final example, we will use the date datatype and demonstrate how to incorporate date values into your CASE custom sorts.

Another request has come in from our manager. They want the data to be displayed in a certain order based on the date column, order_date. Here’s what we were told:

• Display all orders from the current year (2021) and sort these dates by the month and day of the month in ascending order.
• Next, show the dates from the previous years, and also sort these by the day and month.

Let’s write our query and run through our final example of a SQL ORDER BY CASE.

SELECT id, location, count, order_date
FROM orders
ORDER BY 
CASE WHEN EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURDATE()) THEN 1
     WHEN EXTRACT(YEAR FROM order_date) = (EXTRACT(YEAR FROM CURDATE()) - 1) THEN 2
     ELSE 3 
END ASC, order_date ASC

There’s a lot to break down here! Let’s get into it. Examine the following snippet:

CASE WHEN EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURDATE()) THEN 1

EXTRACT is used to get the year from the order date. CURDATE() is used to get the current date. You can see more functions like these in this SQL Standard Functions course that teaches you how to process numerical, text, and other types of data with SQL’s most-used functions.

We compare the year from order_date to the year from the current date. We get this set of data to appear first by setting the sort key to 1.

WHEN EXTRACT(YEAR FROM order_date) = (EXTRACT(YEAR FROM CURDATE()) - 1) THEN 2

This is similar to the last section, except we are using last years date (YEAR FROM CURDATE()) - 1)

Finally, we get the rest of the results to appear using this snippet:

ELSE 3

This time, we've also sorted by order_date ASC. This ensures that the months and days of that month are sorted in ascending order.

Let’s view the result of executing this query:

Our results are appearing exactly how we want them! The latest years are appearing first, and each set of results per year is sorted by the months and days of that year.

SQL ORDER BY Specific Values

That concludes our look at custom sorts. We’ve increased the complexity of each sort with each example. By this stage you should be able to apply the principles from this article to a problem you may be facing. If you need to see more examples, check out this blog post about how to sort records with the ORDER BY clause. This is considered to be quite an advanced SQL topic. To see more advanced SQL problems check out this Advanced SQL Learning Track from LearnSQL.com.