In this article, we’ll walk-through the concept of the SQL Order by clause and understand how the SQL engine works with the ordering result in a query.
Introduction
SQL has been anointed the world’s third most powerful Data Science, Machine Learning software programming language. Most real-time systems use SQL to manage the incredible amount of data they work with on a daily basis. As a result, understanding how a query works in Microsoft SQL Server is a highly valuable skill.
Get started
Let us dive into the SQL Order by clause but first, let’s attempt to understand why data is not ordered in a table by default. To answer this question, let us take a step back and review the concepts of set theory. In mathematical terms, SQL Server uses the table to represent the concept of a Relation. Every relation contains a set of elements that define the properties of a relation and is commonly known as Set. We also learn that a relation is considered to be a set or subset and follow no order. As a result, a SQL Server table guarantees that it has no defined order for the rows found within it.
Guidelines
- A SQL Server result-set is a factor of the way in which the data was entered into the table. In some cases, the data appeared to be in sorted order but this doesn’t mean that data entered and data stored in the hard-drive are same
- SQL Server doesn’t guarantee the order of the result-set. It’s independent of the order unless we define it explicitly on how we want those records sorted. The only way to change the order in which the results appear is to use the SQL Order by clause
- The SQL Order by clause is not valid for in-line functions, views, derived tables, and sub-queries, unless it is specified with SQL TOP or OFFSET and FETCH clauses
- Having constraints, clustered index, identity values or sequences doesn’t guarantee the ordering of the results. Again, if you get an output in the sorted order then it is just a coincidence of the fact that data entered in the table in the same order of the sorting of the data.
- A SQL query that uses set operators such as UNION, EXCEPT, or INTERSECT, SQL Order by is allowed only at the end of the statement
Examples
In this section, we’re going to see a few examples of SQL, so that we can have a better understanding of how we’re actually working with SQL Order by clause. So let’s get started with few samples.
How to use the SQL Order by clause in a SQL query
In the following example we’re going to run the sample SQL without using the SQL Order by clause. The output is a clear indication that when we run a query in SQL Server, the query optimizer looks at the data request and generates a query plan and returns the records from the table or tables, and it’s based on the query and also it is based on how the data is physically organized on the drive.
|
1 2 3 4 5 6 |
SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM Person.Person; GO |
Note: The SQL Order by clause only provides sorting of the records in the results set. The Order by clause does not affect the ordering of the records in the source table or changing the physical structure of the table. It is just a logical re-structuring of physical data.
Next, add the SQL Order By clause in the query. You can see an ordered result-set sorted with ascending order based on the value BusinessEntityID.
|
1 2 3 4 5 6 7 |
SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM Person.Person ORDER BY BusinessEntityID; GO |
In this case, SQL Server uses BusinessEntityID and returns all of the records and it will order the result set based on that specific field.
Note: The default ORDER is ascending order and result-set is sorted in ascending order based on the field that is specified in the SQL query.
How to use Numbers and specify descending order
In the following example we’re going to change the order of the records. In this case, we’re going to use the LastName as the Order by column and explicitly mention the keyword DESCENDING or DESC to specify the sorting order. You also may notice that the number 4 is specified in the order by clause. The number 4 specifies the position of the columns in the SQL query. In this case, position of BusinessEntityID is 1, FirstName is 2, MiddleName is 3 and LastName is 4.
|
1 2 3 4 5 6 7 |
SELECT BusinessEntityID, FirstName, MiddleName, LastName FROM Person.Person ORDER BY 4 DESC; GO |
Note: A sort column can be specified as a name or column alias, or a non-negative integer representing the position of the name or alias in the select list.
How to specify ascending and descending order in the same query
The following example orders the result set on FirstName ascending order and LastName Descending order. The query result set is first sorted based on the ascending order by the FirstName column and then sorted in descending order by the LastName column.
|
1 2 3 4 5 6 7 |
SELECT FirstName, MiddleName, LastName FROM Person.Person ORDER BY FirstName ASC, LastName DESC; GO |
Now, we can see that the data is sorted by the FirstName ascending order and then you’ll see sorted results with a sorting of the LastName descending order. So let’s take a look at the output. In this case, FirstNames is “Aaron” and then we have LastName sorted descending order and you’ll notice LastName field is alphabetical order starting from Zhang, Young, Yang etc.
Now, you can get an idea of how that ORDER BY using two different fields, or two different field names changes the way the result set comes in. Priority is given to sorting on the FirstName, and then within the results of those FirstNames, we will sort the remaining values by the LastName.
How to specify the conditional order
The following examples use the CASE expression in an Order by clause to conditionally determine the sort order of the rows based on a given column value.
In this case, the value of the gender column is evaluated. If the value of the column is M then the corresponding BusinessEntityID is sorted in descending order. If the value found is “F” then the BusinessEntityID column is sorted in an ascending order.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
WITH cte AS (SELECT e.Gender Gender, e.VacationHours VacationHours, p.Rate Rate, e.BusinessEntityID BusinessEntityID FROM HumanResources.Employee e INNER JOIN HumanResources.EmployeePayHistory p ON e.BusinessEntityID = p.BusinessEntityID) SELECT * FROM cte WHERE Rate > 50 ORDER BY CASE GENDER WHEN 'M' THEN BusinessEntityID END DESC, CASE GENDER WHEN 'F' THEN BusinessEntityID END; |
The output shows all the employee details whose rate is over 50 and BusinessEntityID is sorted descending order for male employees and sorted Ascending order for female employees.
How to use ORDER BY with UNION, EXCEPT, and INTERSECT operators
The following example the query uses the UNION ALL operator. You can see that the SQL Order by clause must be specified at the end of the SQL statement and the results of the combined queries are sorted.
- You can learn more on this topic by referring to the article SQL Union for more information.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT TOP 5 FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName LIKE 'A%' UNION SELECT TOP 5 FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName LIKE 'B%' ORDER BY FirstName, LastName DESC; |
Summary
So far we discussed several examples to understand the concepts of SQL Order by clause. We learned that SQL Server doesn’t guarantee any order of the results stored in the table, nor in the results set returned from your queries, but we can sort the output by using the order by clause.
That’s all for now… Thanks for reading this post. Please leave any questions or feedback in the comments below.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021