The SQL ORDER BY clause is used to sort your query result in ascending or descending order. Once you have written a query, you’ll naturally want to reorder the results. You can do so using the ORDER BY clause.
SQL ORDER BY is versatile. Use the ORDER BY keyword to sort results with a SELECT statement. You can sort over multiple columns, in both ascending and descending order.
Table of contents
Please follow along and do the examples in your database. If you haven’t already done so, sign up for my Guide to Getting Started with SQL Server. You get instructions on how to install the free tools and sample database.
Introduction to SQL ORDER BY
Once you get results you want to sort them using the SQL ORDER BY clause. Use it to specify columns or expressions to sort your results.
The general form of the command is:
SELECT column1, column2, column3 FROM table1 ORDER BY column1, column4
In order to sort a query’s results use the ORDER BY clause. This clause comes after the FROM clause and is where you specify columns or expression to use to order your data.
When using the ORDER BY clause the select statement takes the form
SELECT columns FROM table ORDER BY columns;
So if you wanted to sort people by last name you could state
SELECT FirstName, LastName FROM Person.Person ORDER BY LastName
In addition you can specify the direction to sort. Unless specified, all sorts are in ascending order (smallest to largest) and can be explicitly specified using the ASC keyword
SQL ORDER BY Two Columns
You can also order by more than one column. Just separate the columns you wish to sort with a comma. If you wanted to sort Person by Last and First Name.
SELECT FirstName, LastName FROM Person.Person ORDER BY LastName, FirstName
Here are the results
Results are ordered in ascending order (A to Z). Also, when specifying two or more sort columns, the second column values are sorted within the first.
You can see this in the above example, in rows 5 through 8 where the overall result is sorted by LastName, and the FirstName within them. Notice how Hazem and Sam are sorted within the last name Abolorous.
Of course you can also order your results in descending order. To do so use the DESC keyword.
SQL ORDER BY Descending (DESC)
Use the DESC keyword to sort your SQL result in descending order. To do so is easy. Just add DESC, The SQL DESC keyword specifies a descending sort order for a column within in the SQL ORDER BY clause. Values are sorted in Z to A order.
The following statement sorts each Person.LastName in descending order. Since noting is specified for FirstName, they are sorted in ascending order within LastName.
SELECT LastName, FirstName, EmailPromotion FROM Person.Person ORDER BY LastName Desc, FirstName
Keep in mind the default sort order is ascending.
Notice how I added the ASC keyword to the FirstName column. The SQL ASC keyword specifies an ascending sort order for a column within in the ORDER BY clause. This means the values are sorted in A to Z order.
SELECT LastName, FirstName, EmailPromotion FROM Person.Person ORDER BY LastName Desc, FirstName ASC
Add desc to both columns to sort both in descending order. Doing so, avoids having the column sort in ascending order, which as you know is the default value:
SELECT LastName, FirstName, EmailPromotion FROM Person.Person ORDER BY LastName Desc, FirstName Desc
ORDER BY Expression (Calculation)
So far you have learned to sort on one or more columns, but did you know you can sort on a expression?
For instance, consider PurchaseOrderDetail, perhaps you would like to know who has the largest orders. Of course you can sort by Quantity or UnitPrice, but what about TotalPrice? Can we sort on this?
Check out the following, and look closely at the ORDER BY clause, there you’ll see where it’s ordered by TotalPrice (UnitPrice * Quantity)
SELECT PurchaseOrderID, UnitPrice, OrderQty FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice * OrderQty
To make it more clear, lets also display the TotalPrice, which leads to the second way:
SELECT PurchaseOrderID, UnitPrice, OrderQty, UnitPrice * OrderQty AS TotalPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice * OrderQty
And here is where aliasing, or renaming fields, can help. Here UnitPrice * OrderQty is aliased as TotalPrice in the SELECT clause. Now that’s done, we can simply refer to TotalPrice when specifying the sort order.
SELECT PurchaseOrderID, UnitPrice, OrderQty, UnitPrice * OrderQty AS TotalPrice FROM Purchasing.PurchaseOrderDetail ORDER BY TotalPrice
You may be wondering why would I want to sort on calculation when I can just add a column to my SELECT and sort on that?
My answer is that you may want to sort on an item you don’t need to display. In this case, SQL is flexible. It allows your to sort on value that isn’t within your result.
Get TOP Results
The last item I would like to go over with you is being able to limit the number of sorted results returned from a list. This makes it really easy to return the “first ten” or “top ten” items in a search.
Dig Deeper! Read more about SQL TOP
For instance if you want to know the top five PurchaseOrderDetail items, you could enter the following query into SSMS
SELECT TOP 5 PurchaseOrderID, UnitPrice, OrderQty, UnitPrice * OrderQty AS TotalPrice FROM Purchasing.PurchaseOrderDetail ORDER BY TotalPrice
The TOP keyword limits our search to the first five rows. You can limit by other numbers of course, in fact you can also limit by any number resulting from an expression.
To learn more about the ORDER BY, check out these useful resources:
How to use a sub query to get all products that are a specific color?
In Get TOP Results example:
SELECT TOP 5 PurchaseOrderID,
UnitPrice * OrderQty AS TotalPrice
ORDER BY TotalPrice
It should be ORDER BY TotalPrice DESC?
I think DESC is missing. When it is just ORDER BY TotalPrice I am getting the last 5 or the smallest 5 order details.
I did not receive the results expected in Exercise #2 with your posted answer. The last name data was not in upper case. I got it to work by using the code below. Did I do something wrong?
Order by lastname
The answer you gave is good if you want to report the LastName in upper case; however, the question asked to order the results by the uppercase equivalent of the last name. To do that you would ORDER BY upper(LastName).
Thanks for this nice article.
I met an error during doing the exercise 4 “Select the last two names to appear in a sort of Person LastNames.”
Using AdventureWorks2012, if I run
SELECT TOP 2
Person.FirstName + ‘ ‘ + Person.LastName AS FullName
Then I got two identical People:
To remove duplicated select result, I used the DISTINCT keyword in this form:
SELECT DISTINCT TOP 2 Person.FirstName + ‘ ‘ + Person.LastName AS FullName FROM Person.Person ORDER BY LastName DESC
But I got an error
“ORDER BY items must appear in the select list if SELECT DISTINCT is specified.”
That is confusing, could you kindly tell me the right command?
Thanks in advance!
I’m glad you liked the article. The DISTINCT keyword isn’t necessary to arrive at the answer. Actually you had it! The last two people in the list have the same names. Perhaps, because of the way the database is setup, they are both an employee and customer. Anyways, it is a good introduction to the fact that data isn’t perfect.
I do see that my example does have a confusing result, since people may think they are getting duplicate rows. So to help with this I changed the exercise to get the top three items.
Now If you did want to get the last two distinct names in the list, this is how you do it…
SELECT DISTINCT TOP 2
,Person.FirstName + ' ' + Person.LastName AS FullName
ORDER BY LastName DESC
To fix your statement I just added the LastName to the Select list since the error message said I had to do so… :)
The error message was “ORDER BY items must appear in the select list if SELECT DISTINCT is specified.” which basically translates in English to, “Yo, if you’re going to use the DISTINCT keyword and you want to sort your list, you need include what you’re sorting by in the distinct’s select list.”
Thanks for the quick reply and yes that make sense:)
ORDER BY OrderQty, UnitPrice
Here which parameter(OrderQty, or UnitPrice) for sorting will have higher priority?
For the above example, which row, #3 or row #4 will come first? And why
Thanks for your nice tutorial it drive me learning SQL
The precedence for sorting is from left to right. That means that all the values are first sorted by the left most columns, and then working right, sorted by those columns’ values.
Given the following table named Example with columns A and B with the following data
SELECT A, B
ORDER BY A, B
Results in the following sorted result
To directly answer your question, the results are first sorted by OrderQty and then by UnitPrice.