SQL Order By

You can use the SQL ORDER BY clause to sort your query results. You can use this clause to either sort your data in ascending or descending order by one or more columns. In this tutorial, we’ll explore the versatility of ORDER BY and how it can be used in combination with TOP to rank results.

For this tutorial we’ll use the Product table which is show below.

Suppose you wanted to list product in alphabetical order. To do this we can use the ORDER BY clause. Here is an example sorting the results:

/* Answer */
select ProductName, ProductType, Price
from Product
order by ProductName

The ORDER BY is considered a clause and generally comes last in the SQL statement.

SQL ORDER BY Syntax

Here is the simplified syntax for the ORDER BY clause:

select Column1, Column2, ...
from TableName
where condition
order by ColumnName [asc | desc]

Where

• Column1, Column2, ... are the columns you want to retrieve data from.
• TableName is the name of the table you want to retrieve data from.
• condition is the filter criteria for the rows you want to retrieve.
• ColumnName is the specific column you want to sort the results by.
• ASC (optional) specifies sorting in ascending order (default).
• DESC (optional) specifies sorting in descending order.

Below are some more comprehensive examples.

Here is the same example we started with, but now with ProductName sorted in descending order (e.g. Z to A)

select ProductName, ProductType, Price
from Product
order by ProductName desc

Ascending, or sorting A-Z, is the default sort order, so when you to sort from highest to lowers, you need to also include desc after the column.

You can also sort by more than once column.

In this example we sort Products first by ProductType and then ProductName.

/* Answer */
select ProductName, ProductType, Price
from Product
order by ProductType desc, ProductName

When you run this notice how ProductType is sorted in descending order, but ProductName is sorted in ascending order within the type.

Example – Using ORDER BY with WHERE

When using the SQL ORDER BY clause with other clauses, such as WHERE, place the ORDER BY at the end of the statement.

/* Answer */
select ProductName, ProductType, Price
from Product
where ProductType in ('S','P')
order by ProductType desc, ProductName

This example first returns ProductType S or P and then orders the results by ProductType and ProductName. As you continue to work with SQL, you find the clauses are written in a specific order. So far the order for what we have learned is:

1. SELECT
2. FROM
3. WHERE
4. ORDER BY

Now that we have a good grasp on ORDER BY, let’s see how we can use it it rank our results using TOP.

TOP allows us to return the first couple of rows from a sorted result. This comes in handy when ranking, such as finding the “Top 5” items.

This query shows the first five products ordered in ascending order by name.

/* Answer */
select top 5 ProductName, ProductType, Price
from Product
order by ProductName

Now let’s look at how we can get the five highest priced products on the menu. Using TOP makes it easy:

/* Answer */
select top 5 ProductName, ProductType, Price
from Product
order by Price desc

here we get the first five results. Since we want to get the highest priced items, we need to switch the sort order to descending (Highest to Lowest).

Using the product table, find the 6 lowest priced items not in the the ProductType ‘P’.

/* Answer */
select top 6 ProductName, ProductType, Price
from Product
where ProductType <> 'P'
order by Price