How to use SQL ORDER BY

SQL’s ORDER BY command enables you to sort search results based on your individual needs, in either ascending or descending order. Sorting the data in your target table into rows and columns can make your results clearer and more readable. That in turn optimizes and accelerates your data analyses and queries in complex data sets.

What is SQL ORDER BY?

You can use SQL ORDER BY to sort data and results from simple or complex datasets. The command enables you to define how data is ordered in the target table. Additionally, you can specify whether data is sorted in ascending or descending order. So ORDER BY helps deliver query results that are clear and easy to read.

By default ORDER BY delivers results in ascending order, that is from A–Z or from smallest to largest value. If you prefer results in descending order, you can use the optional keyword DESC. You can define one or several target columns for the results.

SQL ORDER BY is important for carrying out organized, fast queries. It’s often used with SELECT, WHERE and other SQL operators and functions like GROUP BY.

What is the syntax of SQL ORDER BY?

The syntax of SQL ORDER BY looks like this:

SELECT  ColumnA, ColumnB, …  or *
FROM  Table
ORDER BY  ColumnA, ColumnB, …  (optional: ASC or DESC)

You can expand on this syntax with further operators and parameters. A WHERE clause, for example, can be used to define criteria that narrow down your query results. The syntax of ORDER BY combined with WHERE looks like this:

SELECT  ColumnA, ColumnB, …  or *
FROM  Table
WHERE  ColumnA  =  'SearchCondition'
ORDER BY  ColumnA, ColumnB, …  (optional: ASC or DESC)

You can also use the following parameters:

• SELECT: You can use SELECT to specify which data you want to analyze. Use an asterisk * to query the entire dataset or enter the names of columns to work with those columns.
• FROM: Specifies which table you want to query data from.
• WHERE: WHERE is an optional clause for defining search criteria that will narrow down your results. Within a WHERE clause you can use operators like =, LIKE or NOT.
• ORDER BY: Defines which column you want to sort your results based on. If you don’t add ASC or DESC, results will by default be sorted in ascending order.

What can SQL ORDER BY be used for?

Here are some practical use cases for SQL ORDER BY:

• Sorting product information based on product name, category or price
• Sorting transactions based on amount, date or name
• Sorting orders based on amount or ratings
• Sorting employees based on salary, contracts, performance or department
• Listing inventory based on amount or name
• Sorting shipments based on ZIP code or tracking number
• Sorting customer lists by region or age

Examples of SQL ORDER BY

We’ll now turn to 3 examples using ORDER BY.

Sorting products based on price

In this example, we’ll sort products from the “Hair” category in ascending order based on price. We use FROM and WHERE to specify which items we want to sort. With ORDER BY and ASC, we specify that we want to sort them in ascending order based on price. The syntax looks like this:

SELECT  *
FROM  Products
WHERE  Category  = 'Hair'
ORDER BY  Price  ASC;

Filtering customers

Let’s say you’re working in a table called “Customers” and want to sort all customers from the US based on their name. You want to sort them in reverse alphabetical order from Z to A, so you add DESC to the ORDER BY command.

SELECT  *
FROM  Customers
WHERE  Country = 'USA'
ORDER BY  Name  DESC;

Sorting based on multiple categories

In this example, you want to sort products from the “Electronics” category based on order quantity. In the case of identical order quantities, you want to sort them based on the country the order came from.

SELECT  *
FROM  Products
WHERE  Category  =  'Electronics'
ORDER BY  OrderQuantity ASC,  Country  DESC;

Are there alternatives to SQL ORDER BY?

There are no alternatives to ORDER BY in SQL with the same function. However, GROUP BY is an SQL command that is frequently used in combination with ORDER BY. GROUP BY can be used to group records based on categories, and ORDER BY can additionally sort those records.

An example: Let’s say you want to group customers’ order quantities based on country and then use the aggregate function AVG to sort based on average order quantity. The syntax for that would look as follows:

SELECT  Country, AVG(OrderQuantity)
FROM  Customers
WHERE  Category = 'Electronics'
GROUP BY  Country
ORDER BY  AVG(OrderQuantity) ASC;

You can also use a HAVING clause to filter the sorted records based on searh criteria and conditions. LIMIT can be used to restrict the result set to a certain number.