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.

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;

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.

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.

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.

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.

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.

Next Tutorial: Working with the GROUP BY Clause

Originally published at https://365datascience.com