Phillip Burton

You already know that to return results, you have to use the SQL SELECT statement. The thing about that is that SQL SELECT just returns rows in a non-deterministic order — in other words, an order determined by the database engine. What if you need a specific order of results turned?

To sort the results of an SQL SELECT statement, you use the ORDER BY clause. It is one of the six standard clauses of the SELECT statement — SELECT, FROM, WHERE, GROUP BY, HAVING and this ORDER BY clause.

Person typing on laptop

In this tutorial, we’ll look at the basics of using ORDER BY, as well as some more advanced SQL ORDER BY topics. By the end, you will understand how to use ORDER BY in your SELECT statements.

Sorting data in SQL

Since SQL retrieves data from a database system, sorting records is a key function in these databases.  When you retrieve more than just a handful of rows or records from a database, you need to be able to sort them in some kind of order so that you can easily find specific rows or records by looking at the list of those retrieved.  

To truly be helpful, a sort clause should be able to sort records by any of the available columns. It should also be able to sort by multiple columns in a specified order and with the order determined by an expression of more than trivial complexity.  The SQL ORDER BY clause can do all of these things.

70-461, 761: Querying Microsoft SQL Server with Transact-SQL

Last Updated September 2022

Bestseller
  • 281 lectures
  • Beginner Level
4.6 (12,244)

From Tables and SELECT queries to advanced SQL. SQL Server 2012, 2014, 2016, 2017, 2019, and 2022 | By Phillip Burton

Explore Course

First, let’s retrieve some records

Let’s start with a basic sort. In SQL, you retrieve records using the SELECT command, in the format SELECT [column_1,column_2,etc.] FROM [table], like this:

SELECT name, city, birth_date, favorite_color

FROM people;

It might produce the following results:

data table, retrieve records

Here’s how to do a basic sort

To sort the records, add the ORDER BY clause, and add the fields or columns you want to sort by:

SELECT name, city, birth_date, favorite_color

FROM people

ORDER BY city;

This sorts the records like this:

basic sort

If you change the ORDER BY clause to this:

ORDER BY favorite_color;

The records are in this order:

order by

Are you sorting those records “up” or “down”?

Notice that the selected column shows the datasets above in alphabetical order. If you sort by a numeric column, they sort from low to high numbers.  

That’s because, by default, the ORDER BY clause sorts the dataset in ascending order. Much of the time, that’s a perfectly reasonable thing to do. Still, there will be times when you want to reverse the order. You can do that using the DESC option, which tells ORDER BY to sort in descending order:

ORDER BY favorite_color DESC;

Now look at the order:

order by desc

Note that there’s also an ASC option, which explicitly tells ORDER BY to sort the result set in ascending order. Since sorting is by ascending order by default, the SQL ORDER BY clause usually doesn’t need you to specify ASC. But there are times when it may come in handy. Both ASC and DESC are the primary methods you will use to order your ORDER BY commands, and, again, data in ascending is the default choice.

Of course, this impacts the rows, not the column list. You’ll see the column in the order specified in the SELECT clause. To sort the data, you would need to pull each column in the order you want it. 

The SELECT * wildcard will pull the entire table of columns. When you initially issue your CREATE TABLE commands, you should keep the columns in the order you want them for when using SELECT *.

Sorting from a user perspective

If you are querying databases in an external program, the DESC option is one of those programming basics that allows you to help the program’s users. You can just give them a basic “Sort By” choice. Allow them to enter or select the sort column, then present them with the sorted list of records. If they needed to find something near the end of the list, they would have to scroll or page down until they found it.  

But if you want to be a little more considerate, you can include a “Descending Order” option, which should automatically implement the DESC option in the ORDER BY clause.  For a long list of records, that could make a significant difference.

When it comes to user interfaces, sorting is often helpful in giving users control over how they view their data. By default, an unsorted and unfiltered list might seem to be entirely random to them; the ability to both sort and filter data ensures that they are getting the data that is most relevant to their search.

But you should always program a default sort, too, so that developers also know that their information will show up in a predictable order. When data returns in an entirely random order, it is harder to troubleshoot.

Sorting via name or number

But what if you aren’t sure which table you’re going to select rows or records from? Maybe you’re writing a program that uses several tables, each with different column names. Maybe the SQL query could request different columns, depending on user input. In either case, you can make your code more generic by sorting based on the relative positions of the columns. You can do this by using column numbers instead of column names:

ORDER BY 1;

This sorts the records by the first column, no matter what the name of the column is:

order by 1

At this point in writing applications, a lot of heavy lifting is done in the programming language itself. For instance, the table might be referred to as “$table” in the PHP language — so the SQL query itself would not need to compensate for these changes.

You should be careful when using these types of interactions between the programming language and the SQL code. It isn’t always the case that the expressions and variables need to be held within SQL; instead, they can be held within the native language of the application.

Using multiple columns in a sort

With a large dataset, you may want to sort not just by one column but also by multiple columns. Suppose, for example, that a large number of people detailed in our database come from the same city; it could be useful to sort their data by their names as well as by city. The ORDER BY clause can refer to multiple columns, with the name (or number) of each separated by a comma:

ORDER BY city, name;

It produces a list ordered first by the city column and then by the name column:

order by city, name

Note that with many SQL implementations, you can mix column names and position numbers like this:

ORDER BY 2, name;

Sorting by multiple columns provides even more granular control over the presented data and can be useful when dealing with exceptionally large data sets. For names and usernames, most applications will want to be able to sort alphabetically, even if there’s another column that is being used as a primary order.

Reversing one column in a sort

You can specify the sort order for an individual column so that, for example, the initial sort is in descending order while the second sort is in default (ascending) order:

ORDER BY city DESC, name;

This is the equivalent of:

ORDER BY city DESC, name ASC;

This reverses the first part of the sort so that the city is ordered in descending order, and the name is ordered in ascending order:

order by city desc, name asc

Alternatively, look at this ORDER BY clause 

ORDER BY city, name DESC;

This leaves city sorted in ascending order and name sorted in descending order:

order by city, name desc

You can explicitly set the sort order for each column with ASC and DESC options. This is important if you want the column in ascending or descending order, such as in a customer’s table or orders table. 

Limiting the queries returned by a sort

In addition to the ORDER BY clause, there’s also the LIMIT or TOP clause, which limits the number of rows retrieved. Which version you use depends on what variant of SQL you are using. For example, SQL Server and Microsoft Access use TOP (the brackets in the example below are, in some circumstances, optional):

SELECT TOP (5) name, city, birth_date, favorite_color

FROM people

ORDER BY city;

 But MySQL uses LIMIT:

SELECT name, city, birth_date, favorite_color

FROM people

ORDER BY city
LIMIT 5;

If you just want to select the first row, you might use “LIMIT 1” or “TOP (1)”. If you need the first 100 rows, you will use “LIMIT 100” or “TOP (100)”. But which rows would you get? Using the ORDER BY clause, you can sort the query results however you desire and then return the first few rows based on the sort. There are also some advanced functions, such as SELECT DISTINCT and the WHERE clause, to further limit the rows returned.

Why is this useful? When returning large numbers of rows, an application will often limit the returned rows on a particular page. From there, users will be able to access other pages, moving backward and forward between them. 

SQL GROUP BY is another way to group and organize queries, but it is a little more advanced than a simple SQL SELECT statement. You can use it to summarize the result set into specific groups. You can use this in conjunction with an INSERT statement, which inserts the results into an existing or new table.

Encountering data type issues

Not all data types can be sorted, and not all data will necessarily sort in the way you expect. Numbers that are stored as numeric data types will sort numerically, and dates stored as date data types will sort chronologically. But, depending on the SQL implementation, numbers or dates stored as text/character data may sort alphabetically. This issue may come up, for example, with data that is part numbers.

For example, suppose I executed the following statement::

SELECT name, city, birth_date, favorite_color

FROM people

ORDER BY birth_date;

If the birth_date field or column was stored as a date type, the results would be sorted chronologically.

order by birth_date

However, if the birth_date field was stored as text, then the results would be sorted in alphabetical order:

order by birth_date, alphabetical order

Again, depending on the SQL implementation, it may not be possible to sort some data types. In Microsoft’s Transact-SQL, for example, you cannot use ORDER BY to sort the following data types: text (a variable-length field with a maximum length of 2 GB; distinct from the sortable character data types), ntext (the Unicode equivalent of the text data type), image, XML, geometry, and geography.

Using expressions during sorting

ORDER BY can take an expression as an argument. This, as you might imagine, opens up a broad range of possibilities since (among other things) it allows you to base the sort order on a potentially very complex set of conditions. For example (again, depending on the SQL implementation and allowable expression syntax), you might use a CASE statement to determine the sort field:

ORDER BY CASE city WHEN ‘San Francisco’ THEN favorite_color

ELSE city

END;

This sorts all records for which the city was San Francisco by favorite_color and all other records by city.

Expressions can become extremely complex. The danger of using more complex expressions is that they can make your queries much harder to read. Utilize them too much, and other programmers may not understand what type of data you’re trying to return.

Sorting with null values

If a column includes null data, you may want to sort the records so that the nulls are at the beginning or end of the list. In Oracle SQL, you can use NULLS FIRST to set the sort so that nulls are listed first, and NULLS LAST so that they are listed last:

ORDER BY favorite_color NULLS FIRST;

Any records with NULL in the favorite_color column are then be listed first:

order by favorite_color NULLS FIRST

Note that in Oracle SQL, NULLS LAST is the default if the sort order is ascending, and NULLS FIRST is the default if the sort order is descending. Since the default sort order is ascending, you do not need to specify both ASC and NULLS LAST. This line:

ORDER BY favorite_color;

and this line:

ORDER BY favorite_color ASC NULLS LAST;

are equivalent.

In non-Oracle SQL implementations, you may be able to use a CASE statement or a similar conditional expression (as shown in the previous section) to set the position of NULLs in the sort.

Advanced sorting with Transact-SQL: COLLATE

In Microsoft’s Transact-SQL, you can specify how the sort will handle things such as accent, case, and character set using the COLLATE argument. Collate is compatible with the char and varchar data types and their Unicode equivalents, nchar and nvarchar.

The COLLATE argument takes a collation name, along with a set of options appended to the name and separated with an underscore. 

If you want to specify that the collation should be case-sensitive and accent-sensitive, you will do so by appending those options:

ORDER BY Name COLLATE Breton_100_CS_AS;

If a sort were case-sensitive, you would sort capital letters separately from lower-case letters. “Orange” and “orange” would appear in different parts of the sort. If you wanted them to be grouped together, then you would specify case insensitivity. Similarly, accent sensitivity controls whether the letters “é”, “è” and “ê” should be sorted with the letter “e”.

The following ORDER BY clause uses a case-insensitive and accent-insensitive sort:

ORDER BY Name COLLATE Breton_100_CI_AI;

Advanced sorting with Transact-SQL: OFFSET and FETCH

Microsoft’s SQL Server 2012 and onwards and Oracle SQL from version 12c onwards also allow you to skip a specified number of records and return a specified number of records, both after the sort. The OFFSET argument returns records starting after the specified number of rows. For example, this command:

ORDER BY Name OFFSET 25 ROWS;

would first sort the records by the Name column, then skip 25 records and return all records after that.

You can use it to quickly page through a large set of sorted records without sending all of the records to the client application. The user can specify the number of records to skip, for example (based on a ballpark estimate of where the desired records are likely to be), then scroll or page from there to find the record(s) in question.

You can use the FETCH argument with OFFSET to specify the number of records to return after the offset. If you want to skip 150 records, then return the next 35, you use this command:

ORDER BY Name

OFFSET 150 ROWS

FETCH NEXT 35 ROWS ONLY;

Both OFFSET and FETCH can take variables or expressions as arguments, so the number of rows to offset and the number of rows to fetch could each be set by conditional expressions or by variables representing user inputs or calculated values.

You can also do this in MySQL by using the LIMIT clause:

ORDER BY Name

LIMIT 150, 35

Do you want to learn more? I look at all six clauses of the SELECT statement and more in my Udemy courses. 

If you would like to learn more about Microsoft’s T-SQL, then please look at my 29-hour “Querying Microsoft SQL Server with Transact-SQL” course. If you would like something shorter, please look at my 8-hour “Database Fundamentals” course or my “SQL Server Essentials in an hour: The SELECT statement.”

If you would like to learn Oracle SQL, please look at my “1Z0-071 Oracle SQL Developer: Certified Associate (Database)” course.

If you are interested in learning about other useful SQL commands, why not delve deeper into the FROM clause and have a look at my Udemy blog about the INNER JOIN clause.

Thank you very much for reading this, and keep learning!

Page Last Updated: May 2022

Top courses in SQL

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo