How to Write SQL Queries | Sort your Results with ORDER BY

So far in this series, we have looked at

• Database fundamentals
• Simple SELECTS
• Column Expressions
• Filter with WHERE
• Using LIKE for Wildcard searches

In this post, we are going to introduce the ORDER BY  clause which you can use to sort the results of your query by a column or set of columns

The Basics of a SELECT query

In the previous post, we introduced the syntax for a SELECT Statement. Let’s revisit that here the syntax is below. Notice that in our SELECT statement syntax, ODRER BY is the last clause, ORDER BY is always last in a SELECT Statement. The more observant of you will see that there are some clauses that we haven’t yet discussed. We will return to  JOINS, GROUP BY, HAVING in future posts. In this post we will focus on ODRER BY using SELECT, FROM, WHERE clauses that we have covered previously

SELECT <Column Expression>,...,<Column Expression> | *
FROM <table> [JOIN <table> ON <join condition>]
WHERE <filter condition>
GROUP BY <column>,...,<column>
HAVING <filter condition>
ORDER BY <column>,...,<column>

The ORDER BY Clause

DO you want the results set of your SQL query returned to you in a given column order? To guarantee that sort order you need to use the ORDER BY clause in your query. Even if the rows appear to be sorted. The sort order could be by coincidence. If you want to sort your results you must use the ORDER BY clause

OK, so you want to sort your results. So, you add an order to your query. How do you specify how to sort? Well you can sort by

• Column name
• Column expression
• Relative column position – so you can say ORDER BY 1 and that will sort on the first column in the results
• You can sort by the column alias… Remember column aliases?

So you say I want to sort on CustomerID do you want the largest or smallest values first. This is where ASC (which is the default behaviour) which sorts lowest to highest and DESC which sorts highest to lowest comes into play.

ORDER BY Clause Examples

Again, I think an example will help demonstrate the ORDER BY clause in action

We’ll use a modified  example from a previous post for this, lets use this query

SELECT CustomerID
,FirstName
,LastName
,ModifiedDate
FROM [SalesLT].[Customer]
WHERE ModifiedDate >= '2007-06-01 00:00:00'
AND ModifiedDate <= '2007-09-30 00:00:00'

If you run this query you will get the following results returned. There is no ORDER BY clause specified so the results in this case come back in an unordered state. If you look closely you’ll see that that the result seem to sorted by the CustomerID column, although that is not guaranteed.

If you would like your result returned in a specific order to need the order by clause.

Now, let’s say we wanted need to sort our results. We want to see the customer’s data sorted on ModifiedDate then we could use this

SELECT CustomerID
,FirstName
,LastName
,ModifiedDate
FROM [SalesLT].[Customer]
WHERE ModifiedDate >= '2007-06-01 00:00:00'
AND ModifiedDate <= '2007-09-30 00:00:00'
ORDER BY ModifiedDate

In this example, we have used ORDER BY DateModified, with no other clause specified.  This will default to ascending. So, we will get the lowest values first. If you are using that on a date field then that means you get the earliest values first.

Do you want the latest values first? We would need to use the ORDER BY clause combined with the DESC Clause. Like this

SELECT CustomerID
,FirstName
,LastName
,ModifiedDate
FROM [SalesLT].[Customer]
WHERE ModifiedDate >= '2007-06-01 00:00:00'
AND ModifiedDate <= '2007-09-30 00:00:00'
ORDER BY ModifiedDate DESC

You can sort on more than one column. So, for example, if we wanted to sort on the ModifiedDate with the  most recently modified  first and then sort that modified group by LastName in alphabetical order we would need to use the following

SELECT CustomerID
,FirstName
,LastName
,ModifiedDate
FROM [SalesLT].[Customer]
WHERE ModifiedDate >= '2007-06-01 00:00:00'
AND ModifiedDate <= '2007-09-30 00:00:00'
ORDER BY ModifiedDate DESC, LastName

Other Useful Information

If you are interested in attending some formal training the check out our Writing SQL Queries training page. If you would like to speak to us directly please use this contact form

Summary

So, if you want your results returned with a specific sort order you need to use the ORDER BY clause. You can then sort on the columns necessary. This can be particularly helpful with analytical queries, and answer questions like “Who were the top 5 salespeople in May?”

Useful Links

Check out our data analysis videos on YouTube

• Database fundamentals – How to Write SQL Queries series.
• Simple SELECTS –  How to Write SQL Queries series.
• Column Expressions – How to Write SQL Queries series.
• Filter with WHERE – How to Write SQL Queries Series
• Using LIKE for Wildcard searches – How to Write SQL Queries

The top 1 tip to better promote your SQL Server Blog

Four SQL Server problems you might be suffering from – PetchaKutcha Style