How To Write SQL ORDER BY Statements

Hey - Nick here! This page is a free excerpt from my $99 course SQL Fundamentals.

If you want the full course, click here to sign up and create an account.

I have a 30-day satisfaction guarantee, so there's no risk (and a ton of upside!) in signing up for this course and leveling up your SQL skills today!

In this lesson, you will learn how to write your first SQL ORDER BY statement.

What is a SQL ORDER BY Statement?

If you have been playing around in pgAdmin4, you may have noticed that SQL will sometimes return results for the same query in different orders. To fix this, we can use ORDER BY.

The way that ORDER BY works is that you apply it to an existing column within a database table to sort the table's rows based on their value in that column. You use the ASC keyword to specify for the values to be ascending, while the DESC keyword can be used to specify that the values are descending.

Here is an example of a very simple ORDER BY statement with the ASC keyword:

SELECT column_name

FROM table_name

ORDER BY column_name ASC;

Here is an example of a very simple ORDER BY statement with the DESC keyword:

SELECT column_name

FROM table_name

ORDER BY column_name DESC;

When neither ASC or DESC is specified in the SQL query, the ASC keyword will be assumed by default.

You will notice that the ORDER BY statement comes at the end of the SQL query. This makes sense if you think about it.

Since many of the other statements we have learned so far (like DISTINCT and WHERE) actually modify which data should be returned to the user, then it is logical that they should come before the ORDER BY statement, which only changes the appearance of the data.

Using ORDER BY on Multiple Columns

You can also create SQL ORDER BY statements on multiple columns.

This is useful when there are duplicate entries for a specific column - the query will sort by one column first, and then the second column whenever it encounters a duplicate entry in the first column.

As an example, you could use the ORDER BY statement to get lists of all the films in the film table grouped together by rating and listed in alphabetical order like this:

SELECT title, rating

FROM film

ORDER BY rating, title;


Sorting by Unselected Columns Using SQL ORDER BY

Like other statements in SQL, it is possible to apply the ORDER BY statement to columns that you have not actually selected.

As an example, let's say that you wanted a list of movies in order of decreasing replacement cost (so the movies with the highest replacement cost show first in the database table). However, you do not actually care to know what the replacement cost really is.

To do this, you could use the following SQL command:


Final Thoughts

In this lesson, you learned how to create your first SQL ORDER BY statement. We will pair this with the LIMIT keyword in the next lesson to learn how to return a predetermined number of rows according to specified criteria.