How to Use the SQL ORDER BY Clause

SQL Tutorials 5 min read
sql order by

How to Use the SQL ORDER BY Clause

5 min read
Blog / SQL Tutorials / How to Use the SQL ORDER BY Clause

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?

data-science-training

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;

The tables starts from the highest number

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.

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? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Working with the GROUP BY Clause

Leave a Reply

Your email address will not be published.

A Free Data Science Career Guide?

Check your email shortly!