SQL Sort: Using the ORDER BY Command, Part 1

sql primary keyTo sort the results of an SQL SELECT statement, you use the ORDER BY command.  In this post, we’ll look at the basics of using ORDER BY; in Part 2, we’ll cover some of the more advanced ORDER BY topics.

Are you interested in learning more about SQL programming?  There are plenty of good online courses to choose from!

Sorting in SQL: The Issues

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 be genuinely useful, 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.  In SQL, the ORDER BY command can do all of these things.

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 , like this:

SELECT name,city,birth_date,favorite_color
FROM people;

Which 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

Do A Basic Sort

To sort the records, you would add ORDER BY:

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

Which would sort 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 were to change the sort command to this:

ORDER BY favorite_color;

The records would be 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

Up Or Down

Notice that the records are always in alphabetical order by the selected column.  If you were to sort by a numeric column, they would be sorted from low number to high number.  That’s because by default, the ORDER BY command sorted in ascending order.  Much of the time, that’s a perfectly reasonable thing to do, of course, but there will be times when you will 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 records in ascending order.  Since ascending order is the default, you generally wont need to specify ASC, but there are times when it may come in handy.)

Be Nice

The DESC option is one of those programming basics that allows you to be nice to the program’s users.  You could 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  —  and if they needed to find something near the end of the list, they could 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 would automatically implement the DESC option with ORDER BY.  For a long list of records, that could make a major difference.

Name Or Number

But what if you aren’t sure which table you’re going to selecting records from?  Maybe you’re writing a program which could be using any 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;

Will 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

Multiple Columns

With a long set of records, you may want to sort not just by one column, but by multiple columns.  Suppose, for example, that a large number of people in our database come from the same city; it would then be 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;

Which would produce 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;

Reversing One Column

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;

Would reverse 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

While this command leaves the initial sort in the default order, and reverses the second sort:

ORDER BY city,name DESC;

Would reverse 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.

We’ll cover some advanced and specialized SQL sorting topics in Part 2.  If you’re interested in online SQL courses, there are a lot to choose from, including beginning and advanced courses, as well as courses in Oracle SQL, Microsoft SQL Server, and MySQL.