Module 2: Sorting Data Using ORDER BY Clause

Previous Tutorial - Next Tutorial

Now we are familiar with the WHERE clause in the T-SQL query statement. In this section, we’ll be going to learn about how to sort the query result by using the T-SQL ORDER by clause. This clause allows us to sort our result in either ascending or descending by specifying the column name. This is the general syntax of the ORDER by clause with the SELECT statement.

In the ORDER by clause, you can specify either a single column or more than one column to sort the result. The ORDER by clause sort the records in ascending order by default. So if you have not specified the ASC keyword, which stands for ascending, so if you are not specifying the ASC keyword, then by default it will sort the result in ascending order.

To sort the record in descending order, you have to use the DESC keyword which is descending. So rather than go into much theory, let's open the SQL server management studio to see this clause in action. Now in the management studio, I have written one query in which I want to return some data from the person table.

Now my requirement is when I execute this query, I want the result in the sorting order based on the last name column. So for that, I need to define the ORDER by clause on the last name column like(ORDER BY LastName).

Now here, please note that the ASC keyword is optional. If you have not specified any ASC keyword, by default it will result in ascending order. Now if I may execute this query, I will get the result in ascending ORDER by this last name column.

First, it would start with the A and then B and so on. In the same way, you can get the result set in descending order by just specifying the DESC keyword. Now when I execute this query, I will get the result in descending ORDER by the last name Column. In this query, I can also specify more than one column to sort the result. So for that, what I need to do is just separate the columns you wish to sort with a comma.

For example, in this query, I want to return the person records sorted by the last name in ascending and first name in descending order. So for that, I need to write a query like(ORDER BY LastName, FirstName DESC). So now when I execute this query, I will get the record of the last name in ascending order and the first name in descending order.

Sorting Data

With T-SQL, you can sort the result by the column ordinal position in the select list, it means to sort the result, you can also specify the column position rather than the column name in the query. But it is considered a bad practice, though.

In this query, the title is in the first position. The last name is in the second position. The first name is in the third position and so on. So this query can be rewritten as follows by using the column ordinal position. This time I need to write the ORDER by clause something like(ORDER BY 2, 3, DESC). Now when I execute this query, I will get the same result that I have got using this query.

So in this way we are asking to order the rows by the second column in the select list, which is the last name, and then by the third column, which is the first name in descending order. So this is equivalent to this ORDER by clause. But this is a bad practice.

For example, suppose in this query you decided to apply some changes and want to return another column, which is the business entity ID column in the first position in the select list, and you apply those changes to the select list, but forgot to change the ordinal position of this column in the ORDER by clause. Now when we change this query, it will look something like this, see, for example, now I want to business entity ID column with the same ORDER by clause. Now when I execute this query, I will get some different results.

In these results, the ordering gets changed because we have added the business entity ID column at the first position. This column becomes first, the title column becomes second, last name column becomes third. In the ORDER by clause, you specify the ordinal positions 2 and 3. In short, the best practice is to refer to column names rather than this column's ordinal position in the query. Now in the results said if you notice that we got the null value in the first position.

In the title column, while now recall that a null represents a missing or unknown value, so when comparing a null to anything, you get the result as unknown. So how null should behave in terms of sorting? In SQL Server, when using an ascending direction, we get the null value first before the non-null values.

Sorting Data by Order

Now let's move on to another concept. Now here this query. What if I have not specified the ORDER by clause. One of the most confusing aspects of working with the T-SQL is understanding when a query result is guaranteed to be ordered versus when it is not. So when I've not specified an ORDER by clause, then is there a guarantee that the rows will be returned in particular orders and if so, then what is that order?

Some of you might assume that the rows will be returned in an insertion order or some of you might make an assumption that it may return in clustered index order. So to understand the difference, let me write another query.

Now, when I execute these two queries together, notice the ordering difference between these two result sets. Now, notice the first query returns the result in ascending order by this business entity ID column, but surprisingly, when the second results set, it returns the result in an unordered.

Now, why did this happen? The database engine determines the choices that can affect the order in which rows are returned. So many changes in choices include the availability of resources like CPU and memory or the availability of clustered or non clustered indexes created on the table. So what is cluster and non-cluster index is out of scope for this course. In summary, unless you explicitly insert the query using an ORDER by clause or otherwise, the result of a query has no guaranteed order.

If you need the result order head and ORDER by clause. But here, one thing to keep in mind is that if you don't need to return the data as order or sorted, make sure you have not specified the ORDER by clause because sorting can be expensive, especially when a large data set is involved. Now in the next slide, we will learn about the GROUP by clause. We will see how GROUP by clause defines a group for each distinct combination of values in the grouped elements.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials: