Ordering the Results (ORDER BY Clause)

Scripts you might need: UserCreation, TablesCreation
If you are using LiveSQL: LiveSQL_1

In this lesson, you are going to learn how to order or sort the results of your queries by using the ORDER BY clause.

In most real-work situations you will want to get the results of your queries in some specific order, and that is achieved by means of the ORDER BY clause, which is actually very simple and easy to use.

To specify an order for your query you simply add the ORDER BY clause after the WHERE clause, or after the FROM clause if there is no WHERE clause, and specify the columns by which you want to sort the results.

Let’s see an example:

If I want to list the employees ordered by the birth date, I just need to add an ORDER BY clause like this one:

SELECT id, name, birthdate, salary
FROM employee
ORDER BY birthdate;

Or if I want them order by salary, I just do this:

SELECT id,name, birthdate, salary
FROM employee
ORDER BY salary;

By default, the ORDER BY clause sorts the columns in ascending order (or in other words, lower values appear first), but you can use the DESC keyword to sort in descending order.

So, if I add it to my last example, I get the employees with higher salaries first:

SELECT id, name, birthdate, salary
FROM employee
ORDER BY salary DESC;

Results:

ID NAME BIRTHDATE SALARY
10 IGOR KEYS OSBOURNE 11-sep-1987 6000
11 LUKE MINT 19-oct-1985 5000
12 NIGEL OAKS 05-nov-1997 4750
13 LUKE GREEN JR 05-feb-1995 4750
3 LUISA JACKSON 08-mar-1970 4500
1 JOHN SMITH 01-jan-1995 4000
2 JAMES BOSH 15-feb-1992 3500
8 EDDIE FARREL 28-jul-1980 3000
5 JUSTIN BLACK 16-may-1990 2550
9 GEORGE HAYES 03-aug-1982 2500
7 CHARLES DEAN 08-jun-1973 2250
4 STUART GARCIA 12-apr-1965 2000
6 ANGIE CROOD 22-jun-1998 1500

You can also add the ASC keyword to specify that you want to order in ascending order, but as I mentioned earlier, that is the default behavior, so you don’t really need to do it.

Ordering by Two or more Columns

You may want to apply more than one sorting criteria, for example, if you want to order by department_id and then by salary.  To do that,  you just need to specify the 2 columns in the ORDER BY clause.

SELECT id, name, department_id, salary
FROM employee
ORDER BY department_id,salary;

Results:

ID NAME DEPARTMENT_ID SALARY
6 ANGIE CROOD 1 1500
5 JUSTIN BLACK 1 2550
8 EDDIE FARREL 1 3000
1 JOHN SMITH 1 4000
9 GEORGE HAYES 2 2500
2 JAMES BOSH 2 3500
7 CHARLES DEAN 3 2250
3 LUISA JACKSON 3 4500
10 IGOR KEYS OSBOURNE 3 6000
4 STUART GARCIA 4 2000
12 NIGEL OAKS 4 4750
13 LUKE GREEN JR 4 4750
11 LUKE MINT 4 5000

How does it work?

Well, Oracle will order by department_id first, and then, if there are 2 or more rows with the same department_id, they will be ordered by salary, as you see in the results above.

And you can use a different type of ordering for each column.   So, I can add the DESC keyword to the salary column to get the results ordered by department id in ascending order, which is the default, and then by salary in descending order.

SELECT id, name, department_id, salary
FROM employee
ORDER BY department_id, salary DESC;

The Most Important Thing to Remember about the ORDER BY Clause:

If the order of the results is important to you, you must use an ORDER BY clause to specify it, even if the results are returned in such order without doing so.  That’s the only way to guarantee that you will the results in the order you want.

Alternate way to define the ordering criteria

You can also use a column position number in the ORDER BY clause to indicate which column to sort by: 1 means sort by the first column selected, 2 means sort by the second column, and so on.

For example, our previous query which ordered the results by department_id and salary can be written this way:

SELECT id, name, department_id, salary
FROM employee
ORDER BY 3,4;

Or If I want to order by employee id, I can simply write:

SELECT *
FROM employee ORDER BY 1;

Which is a lot easier than writing the column name.

But, for readability and maintainability purposes, I don’t recommend using the column positions in the ORDER BY clause of queries that are part of a script or an application.  Only do that for one-time queries.

Before concluding this lesson, there is something else you need to know about ordering results.  Ordering by columns that include nulls may need some special treatment. We haven’t talked about NULLs until now, but the next lesson is completely devoted to them.

Okay.  This lesson doesn’t have a task for you, but don’t be sad.  You will practice this topic very soon.

See you later!

Keep Learning Oracle SQLMake sure to subscribe so that you don’t miss any new lesson  😎

*Please note that this is a different subscription, specific to this tutorial and separate from the blog.