The SQL ORDER BY clause and functionality is an important part of a SELECT statement. Learn what it does and how to use it in this article.
What Is The SQL ORDER BY Clause?
The SQL ORDER BY clause allows you to order your results. You can specify what you want to order by, and can even order by multiple columns.
By default, the SQL results are not ordered in any specific order. Without the ORDER BY clause in your SQL query, the results may look like they are in a certain order. However, the Oracle database cannot guarantee that the results will always be in the same order.
So, if you need results to be ordered, then add the ORDER BY clause.
Syntax and Parameters of SQL ORDER BY
The syntax of the SQL ORDER BY clause is:
ORDER BY {column_name | column_position | expression}
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
In this clause:
- column_name is one of the columns in your SELECT clause or in your table that you want to order by.
- column_position is a number that refers to the position of a column in your SELECT statement.
- expression is a valid SQL expression that you want to order your results by
- ASC or DESC can be used to specify the order of the data. ASC is ascending, and DESC is descending. This is optional, and if it is not provided, the default sort order is ASC.
- NULLS FIRST or NULLS LAST can be used to specify how NULL values are sorted. This only applies to Oracle SQL. NULLS FIRST means that NULL values are shown at the top of the list, and NULLS LAST means that NULL values are shown at the bottom of the list. The default treatment if this is not specified is NULLS FIRST if the sort is DESC, or NULLS LAST if the sort is ASC or not specified.
Also, the column that you order by does not need to exist in the SELECT clause. So you don’t need to see the column in your results to use it in the ORDER BY.
Examples
I think the easiest way to learn is to see examples. So, I’ll show you a few ways you can use the SQL ORDER BY clause in Oracle.
I’ll be using this table to perform the SELECT queries on.
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
1 | John | 90000 | 1000 | 1-Jan-16 |
2 | Sally | 95000 | 500 | 5-Sep-16 |
3 | Mark | 101000 | 800 | 12-Aug-16 |
4 | Tina | 87000 | 900 | 24-Oct-16 |
5 | Steve | 100000 | 500 | 2-Feb-16 |
6 | Michelle | 120000 | 600 | 3-Dec-16 |
7 | Alex | 85000 | (null) | 17-Jan-16 |
8 | Jo | 115000 | 1200 | 30-Oct-16 |
Example 1 – ORDER BY Column Name
This example orders by a single column name.
SELECT
salesperson_id,
first_name,
salary,
commission,
hire_date
FROM salesperson
ORDER BY first_name;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
7 | Alex | 85000 | (null) | 17-Jan-16 |
8 | Jo | 115000 | 1200 | 30-Oct-16 |
1 | John | 90000 | 1000 | 1-Jan-16 |
3 | Mark | 101000 | 800 | 12-Aug-16 |
6 | Michelle | 120000 | 600 | 3-Dec-16 |
2 | Sally | 95000 | 500 | 5-Sep-16 |
5 | Steve | 100000 | 500 | 2-Feb-16 |
4 | Tina | 87000 | 900 | 24-Oct-16 |
All of the records are ordered by the first_name column. ASC or DESC was not specified, so by default, they are ordered in ASC order.
Example 2 – ORDER BY Column Name using ASC
This example orders by a column name and uses the ASC keyword.
SELECT
salesperson_id,
first_name,
salary,
commission,
hire_date
FROM salesperson
ORDER BY salary ASC;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
7 | Alex | 85000 | (null) | 17-Jan-16 |
4 | Tina | 87000 | 900 | 24-Oct-16 |
1 | John | 90000 | 1000 | 1-Jan-16 |
2 | Sally | 95000 | 500 | 5-Sep-16 |
5 | Steve | 100000 | 500 | 2-Feb-16 |
3 | Mark | 101000 | 800 | 12-Aug-16 |
8 | Jo | 115000 | 1200 | 30-Oct-16 |
6 | Michelle | 120000 | 600 | 3-Dec-16 |
This sorts the data in the table by salary in ascending order, which for numbers, is from smallest to highest.
Example 3 – ORDER BY Column Name using DESC
This example orders by a column name and uses the DESC keyword.
SELECT
salesperson_id,
first_name,
salary,
commission,
hire_date
FROM salesperson
ORDER BY salary DESC;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
6 | Michelle | 120000 | 600 | 3-Dec-16 |
8 | Jo | 115000 | 1200 | 30-Oct-16 |
3 | Mark | 101000 | 800 | 12-Aug-16 |
5 | Steve | 100000 | 500 | 2-Feb-16 |
2 | Sally | 95000 | 500 | 5-Sep-16 |
1 | John | 90000 | 1000 | 1-Jan-16 |
4 | Tina | 87000 | 900 | 24-Oct-16 |
7 | Alex | 85000 | (null) | 17-Jan-16 |
This sorts the data in the table by salary in descending order, which for numbers, is from highest to smallest.
Example 4 – ORDER BY Column Number
This example shows you how to order your results by specifying a column number.
SELECT
salesperson_id,
first_name,
salary,
commission,
hire_date
FROM salesperson
ORDER BY 2 DESC;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION | HIRE_DATE |
4 | Tina | 87000 | 900 | 24-Oct-16 |
5 | Steve | 100000 | 500 | 2-Feb-16 |
2 | Sally | 95000 | 500 | 5-Sep-16 |
6 | Michelle | 120000 | 600 | 3-Dec-16 |
3 | Mark | 101000 | 800 | 12-Aug-16 |
1 | John | 90000 | 1000 | 1-Jan-16 |
8 | Jo | 115000 | 1200 | 30-Oct-16 |
7 | Alex | 85000 | (null) | 17-Jan-16 |
This sorts the results by the second column in the SELECT clause. In this case, it is the first_name column (salesperson_id is column 1, first_name is column 2, salary is 3, and so on).
This is the order of the columns in the SELECT clause, and not the table.
I try to avoid using column numbers when ordering, as it’s not clear which column is being ordered. Also, if the sequence that the columns are mentioned in the SELECT clause is changed, then the ordering will break, or order by the incorrect values.
Example 5 – ORDER BY a Column Alias
This example shows you how you can order by a column alias. This is very helpful to reduce the amount of code you write and to keep your logic in one place (the SELECT clause, for example).
SELECT
salesperson_id,
first_name,
salary + commission AS total_earnings
FROM salesperson
ORDER BY total_earnings DESC;
Result:
SALESPERSON_ID | FIRST_NAME | TOTAL_EARNINGS |
7 | Alex | (null) |
6 | Michelle | 120600 |
8 | Jo | 116200 |
3 | Mark | 101800 |
5 | Steve | 100500 |
2 | Sally | 95500 |
1 | John | 91000 |
4 | Tina | 87900 |
This query shows the salespeople in order of their total earnings. Using a column alias in the SQL ORDER BY clause is helpful, especially when working with complicated functions.
Example 6 – ORDER BY an Expression
This example shows how you can order by an expression.
SELECT
salesperson_id,
first_name,
salary/52
FROM salesperson
ORDER BY salary/52 DESC;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY/52 |
6 | Michelle | 2307.692308 |
8 | Jo | 2211.538462 |
3 | Mark | 1942.307692 |
5 | Steve | 1923.076923 |
2 | Sally | 1826.923077 |
1 | John | 1730.769231 |
4 | Tina | 1673.076923 |
7 | Alex | 1634.615385 |
This query shows the weekly salary of each salesperson.
Example 7 – ORDER BY Function
This example shows how you can use ORDER BY with a function.
SELECT
salesperson_id,
first_name,
FLOOR(salary/52)
FROM salesperson
ORDER BY FLOOR(salary/52) DESC;
Result:
SALESPERSON_ID | FIRST_NAME | FLOOR(SALARY/52) |
6 | Michelle | 2307 |
8 | Jo | 2211 |
3 | Mark | 1942 |
5 | Steve | 1923 |
2 | Sally | 1826 |
1 | John | 1730 |
4 | Tina | 1673 |
7 | Alex | 1634 |
You can see that the results have been ordered by the function value.
Example 8 – ORDER BY with NULLS FIRST (Oracle)
This example shows how the NULLS FIRST keyword works.
SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY commission NULLS FIRST;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
7 | Alex | 85000 | (null) |
2 | Sally | 95000 | 500 |
5 | Steve | 100000 | 500 |
6 | Michelle | 120000 | 600 |
3 | Mark | 101000 | 800 |
4 | Tina | 87000 | 900 |
1 | John | 90000 | 1000 |
8 | Jo | 115000 | 1200 |
As you can see, the NULL values are shown at the top of the list.
Example 9 – ORDER BY with NULLS LAST
(Oracle)
This example shows how the NULLS LAST keyword works.
SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY commission NULLS LAST;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
2 | Sally | 95000 | 500 |
5 | Steve | 100000 | 500 |
6 | Michelle | 120000 | 600 |
3 | Mark | 101000 | 800 |
4 | Tina | 87000 | 900 |
1 | John | 90000 | 1000 |
8 | Jo | 115000 | 1200 |
7 | Alex | 85000 | (null) |
As you can see, the NULL values are shown at the bottom of the list.
Example 10 – ORDER BY Two Columns
This example shows how you can use SQL ORDER BY with two columns.
SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY commission, salary;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
2 | Sally | 95000 | 500 |
5 | Steve | 100000 | 500 |
6 | Michelle | 120000 | 600 |
3 | Mark | 101000 | 800 |
4 | Tina | 87000 | 900 |
1 | John | 90000 | 1000 |
8 | Jo | 115000 | 1200 |
7 | Alex | 85000 | (null) |
This query orders by the commission values in ascending order, then for records where the commission is the same, it orders by salary in ascending order.
Example 11 – ORDER BY Two Columns in Different Order
This example orders by two columns, but they are in a different order.
SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY commission ASC, salary DESC;
Result:
SALESPERSON_ID | FIRST_NAME | SALARY | COMMISSION |
5 | Steve | 100000 | 500 |
2 | Sally | 95000 | 500 |
6 | Michelle | 120000 | 600 |
3 | Mark | 101000 | 800 |
4 | Tina | 87000 | 900 |
1 | John | 90000 | 1000 |
8 | Jo | 115000 | 1200 |
7 | Alex | 85000 | (null) |
This query orders by the commission values in ascending order, then for records where the commission is the same, it orders by salary in descending order.
Common Questions
Here are some common questions when it comes to using the Order By clause.
What Is SQL Order By 1?
ORDER BY 1 means that the results of the query are ordered by the first column specified in the SELECT clause.
You might see an SQL query that has “ORDER BY 1” in it:
SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY 1;
This would order the results by the first column in the SELECT clause, which is the salesperson_id column in this example.
What Is SQL Order By 2?
This means that the results of the query are ordered by the second column specified in the SELECT clause.
For example:
SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY 2;
This would order the results by the first_name column as it is the second column in the SELECT list.
You can change the ORDER BY 2 to any other number, up to the number of columns in the select clause.
ORDER BY 3 will order by the third column, for example.
Can we use WHERE with ORDER BY?
Yes, you can use the WHERE clause with or without the ORDER BY clause.
When you use WHERE in a query with ORDER BY, the results are filtered to only those that match the WHERE clause, and then the results are ordered based on what criteria you specified in the ORDER BY clause.
Which comes first – ORDER BY or WHERE?
The WHERE clause must come before the ORDER BY clause in the SELECT query. In almost all cases, the ORDER BY clause is the last clause in the query.
Can we use two columns in the ORDER BY clause?
Yes, you can use two columns (or as many columns as you like). You can separate each column with a comma, and can even specify ascending or descending for each column.
For example:
SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY commission ASC, salary DESC;
This query will order the results by commission in ascending order and then salary in descending order.
Conclusion
So, that’s how you can use the SQL ORDER BY clause in Oracle SQL to order your results. It’s a powerful clause and has a few keywords to get you the result that you need.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
I would suggest mentioning that the column/expression in the ORDER BY clause does not have to be used in SELECT clause.
Great point Zahar, I’ll update the article!
I would suggest explaining how ORDER BY can be used in queries with CTE queries.
You make no mention of CASE, is that not an option?
Order by can be used along with case as well