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;
|10||IGOR KEYS OSBOURNE||11-sep-1987||6000|
|13||LUKE GREEN JR||05-feb-1995||4750|
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;
|10||IGOR KEYS OSBOURNE||3||6000|
|13||LUKE GREEN JR||4||4750|
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!
*Please note that this is a different subscription, specific to this tutorial and separate from the blog.