You already know that to return results, you have to use the SQL SELECT statement. The thing about that is, SQL SELECT just returns rows in ascending order. What if you need a specific order of results turned?

To sort the results of an SQL SELECT statement, you use the ORDER BY command. 

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 for most commands.

The Complete SQL Bootcamp 2021: Go from Zero to Hero

Last Updated May 2021

Bestseller
  • 83 lectures
  • All Levels
4.7 (104,721)

Become an expert at SQL! | By Jose Portilla

Explore Course

Sorting data in SQL

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

To truly be helpful, a sort command 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 command can do all of these things.

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:

Bob  San Diego  06/07  blue
Cindy Kuala Lumpur 05/09  orange
Jane  Singapore  12/27 green
Lin  San Francisco  01/17  red
Randy  Berlin  10/21  yellow

Here’s how to do a basic sort

To sort the records, add ORDER BY:

SELECT name,city,birthday,favorite_color
FROM people
ORDER BY city;

This sorts the records like this:

Randy  Berlin  10/21  yellow
Cindy Kuala Lumpur 05/09  orange
Bob  San Diego  06/07  blue
Lin  San Francisco  01/17  red
Jane  Singapore  12/27 green

If you change the sort command to this:

ORDER BY favorite_color;

The records are in this order:

Bob  San Diego  06/07  blue
Jane  Singapore  12/27 green
Cindy Kuala Lumpur 05/09  orange
Lin  San Francisco  01/17  red
Randy  Berlin  10/21  yellow

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

Notice that the records are always in alphabetical order by the selected column. If you sort by a numeric column, they sort from low number to high number.  

That’s because, by default, the ORDER BY command sorts it 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:

Randy  Berlin  10/21  yellow
Lin  San Francisco  01/17  red
Cindy Kuala Lumpur 05/09  orange
Jane  Singapore  12/27 green
Bob  San Diego  06/07  blue

Note that there’s also an ASC option, which explicitly tells ORDER BY to sort the result set in ascending order. Since it’s usually in ascending order by default, the SQL order by clause usually doesn’t need you to specify. 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 statement. 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 CREATE TABLE, you should keep the columns in the order you want them.

Sorting from a user perspective

The DESC option is one of those programming basics that allows you to be nice to 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 can 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 automatically implements the DESC option with ORDER BY.  For a long list of records, that could make a major difference.

When it comes to user interfaces, sorting is often helpful in giving users control over the way that they view their data. By default, a 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 there should always be a default sort, too, so that developers also know that information is going to show up in a predictable order. When data returns in an entirely random order, it only makes it harder to troubleshoot.

Sorting via name or number

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

ORDER BY 1;

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

Bob  San Diego  06/07  blue
Cindy Kuala Lumpur 05/09  orange
Jane  Singapore  12/27 green
Lin  San Francisco  01/17  red
Randy  Berlin  10/21  yellow

At this point in writing applications, a lot of heavy lifting is done in the programming language itself. In PHP, for instance, the table might be “$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 long set of records, you may want to sort not just by one column but also by multiple columns. Suppose, for example, that a large number of people in our database come from the same city; it’s then useful to sort them by their names as well as by city. ORDER By can take 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:

Randy  Berlin  10/21  yellow
Adrenne  San Francisco 03/05 chartreuse
Lin  San Francisco  01/17  red
Xochtil  San Francisco 11/12 turquoise
Jane  Singapore  12/27 green

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

ORDER BY 2,name;

Sorting by multiple columns provides even more granular control over the data presented and can be pretty 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 reverses the first part of the sort:

Jane  Singapore  12/27 green
Adrenne  San Francisco 03/05 chartreuse
Lin  San Francisco  01/17  red
Xochtil  San Francisco 11/12 turquoise
Randy  Berlin  10/21  yellow

This command leaves the initial sort in the default order and reverses the second sort:

ORDER BY city,name DESC;

This reverses the second part of the sort:

Randy  Berlin  10/21  yellow
Xochtil  San Francisco 11/12 turquoise
Lin  San Francisco  01/17  red
Adrenne  San Francisco 03/05 chartreuse
Jane  Singapore  12/27 green

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 customers table or orders table that has to be linked. 

It’s important to note that most queries are going to return an ASC command by default. But that also depends on what the query perceives as the major identifier. If you have a list of people, it’s going to sort on the unique ID or primary key, rather than the person’s name, unless otherwise specified.

Limiting the queries returned by a sort

In addition to the ORDER BY query, there’s also the LIMIT query. The LIMIT query limits the number of rows you receive back. So, if you just want to select top results (the first row), you might use “LIMIT 1.” If you need the first 100 rows, you would use “LIMIT 100.” And you can still sort the query results however you desire. There are also some advanced functions, such as SELECT DISTINCT, to further limit the queries returned.

Further, you can “LIMIT 10,20” if you want to reveal 20 records but start at record 10.

Why is this useful? Many times, when returning large numbers of records, an application will limit the returned records to a certain amount. From there, users will be able to page through the other records, moving backward and forward between them. 

SQL GROUP 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 sort the result set into specific groups. A further advanced statement is INSERT INTO SELECT, which allows you to create a SELECT query that also functions as an INSERT, though you must match the types.

Encountering data type issues

Note that not all data types can be sorted, and not all data will necessarily sort in the way you expect. Numbers that you store 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 that are stored as text/character data may sort alphabetically. This is an issue that may come up, for example, with part numbers.

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

We mentioned that 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. But the danger of 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:

Hua Los Angeles 05/14 NULL
Raquel Bogota 11/07 NULL
Bob  San Diego  06/07  blue
Jane  Singapore  12/27 green
Cindy Kuala Lumpur 05/09  orange
Lin  San Francisco  01/17  red
Randy  Berlin  10/21  yellow

Note that 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 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. For example, the following command would sort records by name, using Breton collation:

ORDER BY Name COLLATE Breton_100_;

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

ORDER BY Name COLLATE Breton_100_CS_AS;

Of course, this will not apply if you’re using Oracle DB or you’re using MySQL.

Top courses in Development

The Complete 2021 Web Development Bootcamp
Dr. Angela Yu
4.7 (141,444)
Bestseller
Complete C# Unity Game Developer 3D
Ben Tristem, Rick Davidson, GameDev.tv Team
4.7 (28,529)
Bestseller
2021 Complete Python Bootcamp From Zero to Hero in Python
Jose Portilla
4.6 (374,175)
Bestseller
The Complete JavaScript Course 2021: From Zero to Expert!
Jonas Schmedtmann
4.7 (110,604)
Bestseller
The Data Science Course 2021: Complete Data Science Bootcamp
365 Careers, 365 Careers Team
4.5 (94,631)
Bestseller

More Development Courses

Advanced sorting with Transact-SQL: OFFSET and FETCH

Transact-SQL also allows you to skip a specified number of records and to return a specified number of records, both after the sort. The OFFSET argument (available starting with SQL Server 2012) 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 find out a lot more about SQL by taking some of the excellent courses that are available online, including beginning and advanced lessons in MySQL, Oracle, and SQL Server.

And if you’re interested in learning about another useful SQL command, let’s get started with the INNER JOIN command.

Top courses in SQL

SQL and PostgreSQL: The Complete Developer's Guide
Stephen Grider
4.7 (1,854)
Highest Rated
SQL Programming Basics
Global Academy
4.5 (367)
The Complete SQL Bootcamp 2021: Go from Zero to Hero
Jose Portilla
4.7 (104,721)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (56,909)
Bestseller
Master SQL For Data Science
Imtiaz Ahmad
4.7 (7,993)
Advanced SQL : SQL Expert Certification Preparation Course
Oracle Master Training • 100,000+ Students Worldwide, Code Star Academy
4.5 (2,788)
Bestseller
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jon Avis - SQL Instructor
4.5 (13,819)
Learn SQL +Security(pen) testing from Scratch
Rahul Shetty
4.6 (1,722)
Bestseller
SQL & PostgreSQL for Beginners: Become an SQL Expert
Jon Avis - SQL Instructor
4.6 (3,486)
Bestseller

More SQL Courses

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 for Business.

Request a demo