How to Use the SQL ORDER BY Clause

Join over 2 million students who advanced their careers with 365 Data Science. Learn from instructors who have worked at Meta, Spotify, Google, IKEA, Netflix, and Coca-Cola and master Python, SQL, Excel, machine learning, data analysis, AI fundamentals, and more.

Start for Free
Martin Ganchev Martin Ganchev 25 Apr 2023 5 min read

If you want to refine your output when coding, the SQL ORDER BY clause is an awesome way to go about it. However, in order to fully understand how to work with it, you should check out what operators we will be using first.

Let’s see what happens when we retrieve all records from the “employees” table. As shown in the picture below, the list we get is automatically ordered based on employee numbers.

A list ordered by employee number

Assume your boss has just asked you to order the people by first name, instead of by employee number. So, how can you do that?

When to Use the SQL ORDER BY clause

Adding “ORDER BY first name” at the end of this query will provide the desired outcome.

SELECT
    *
FROM
    employees
ORDER BY first_name;

The list organised in alphabetical order

As you can see in the picture above, the entire list was reorganized in alphabetical order, according to the field containing employee names.

Specifying the Order

One of two specific reserved words can be attached at the end of the ORDER BY clause.

Ascending Order

The first one is ASC, abbreviated from “ascending”, requiring the output to be sorted by the values in the designated field in ascending order. If this keyword is not attached to the statement at the end, SQL will implicitly understand you want things ordered precisely in ascending order anyway.

ASC organizes in ascending order

Therefore, if we add ASC at the end and re-run the query, we will obtain the same output.

Descending Order

The alternative is to use DESC, which is abbreviated from “descending”. Hence, if you would like your results plotted in reverse order, DESC is the keyword to add at the end of the ORDER BY clause.

Let’s check if this places the names starting with Z first.

Desc organizes in descending order

Working with Numbers

Now, the SQL ORDER BY clause does not only work for columns containing string values. It can handle numbers as well! Let’s sort the list by employee number in descending order. Our query will look like this:

SELECT
    *
FROM
    employees
ORDER BY emp_no DESC;

This way, we start with a larger employee number that decreases as we scroll down!

Ordering by More than One Field

Let’s see another interesting feature of this clause. You can order your results by more than one field. For instance, we can order employees by first and last name.

To make the comparison, let’s re-run the query where we sorted our output by first name in ascending order.

The list with first names in ascending order

Now, look at the order of last names.

We can sort employees sharing the same first name according to their last name. To do this, we must simply type:

SELECT
    *
FROM
    employees
ORDER BY first_name, last_name ASC;

By writing this “, last_name”, we designated the second column of interest.

All people with the same first name are sorted by last name

Now all people with the same first name are ordered by their surname.

Whate Are the Benefits of Ordering Data?

To sum up, the SQL ORDER BY clause is not mandatory. Rather, it is a helpful tool. Whenever you need to sort your data in ascending or descending order, you can use it.

Otherwise, there’s yet another extremely powerful tool that will allow you to retrieve more meaningful data by avoiding repetition - The GROUP BY clause.

***

Eager to hone your SQL skills? Check out our SQL course.

Next Tutorial: Working with the GROUP BY Clause

Martin Ganchev

Martin Ganchev

Instructor at 365 Data Science

Martin holds an MSc degree in Economic and Social Sciences from Bocconi University. His diverse academic and research experience combined with his friendly and explanatory approach to teaching have made him one of the most beloved instructors on our team. Some of the courses he has authored include: SQL, SQL + Tableau, SQL+Tableau+Python, Introduction to Python, Introduction to Jupyter, to name a few.

Top