SQL ORDER BY – Sorting Results With Multiple Columns

When a data set is returned from a database query, the result may not produce an output in the sort order that we need. We can resolve this by adding the SQL ORDER BY clause to the end of our query. In fact, we can use SQL to order by multiple columns.

SELECT * FROM Customer ORDER BY LastName; — Order by single column

order by single column with results

By adding the ORDER BY clause, we can see the list of last names appear in alphabetical order. We can even be more specific by adding additional columns for ordering. As we can see in the following sql example we can order by multiple columns. In fact, we can sort our data using SQL by as many columns as needed.

SELECT * FROM Customer ORDER BY LastName, FirstName; — SQL Order by 2 things

SQL order by 2 things
Order by multiple columns

By default, the ORDER BY clause will sort the data in ascending order but for peace of mind we can add the ASC syntax for clarification.

SELECT * FROM Customer ORDER BY LastName ASC; — SQL Order by single column ascending

Or if we want to order descending we can alter the ending syntax.


SQL ORDER BY clause descending

Now our results show last names starting from ‘Zimmermann’. We can even sort first name descending and then last name ascending. Just be aware that SQL will always sort by the column order specified meaning if we modify the column order in the ORDER BY clause, we would expect a different result.

SELECT * FROM Customer ORDER BY FirstName DESC, LastName ASC;

One last tip, we can use short-hand when writing an ORDER BY in SQL by declaring the column number in our select statement. For example:

SELECT FirstName, LastName FROM Customer ORDER BY 2;

Since the LastName field is our 2nd column in the SELECT statement, that is what our ORDER BY clause is actually using. Had we switched the first and last names in the SELECT, we would be ordering by FirstName instead. Additionally, we could change the ORDER BY clause to 1 if we wanted to sort by FirstName.

SQL order by with numbers

Again, we can see that if we use SQL to order by 2 things then it’s possible to use numeric values rather than column names. Either of these options work and are valid although using column names is easier to read.

If by some reason you accidentally sorted by 3, the database would return an error. This value is considered out of range since we’ve only selected 2 columns.

SQL error 1st ORDER BY term out of range - should be between 1 and ?

Next Section: UNION vs UNION ALL – Combining Datasets