FB

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.SQL ORDER BY - The Complete Guide

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.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

 

Syntax and Parameters of SQL ORDER BY

The syntax of the Oracle 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. 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_IDFIRST_NAMESALARYCOMMISSIONHIRE_DATE
1John9000010001-Jan-16
2Sally950005005-Sep-16
3Mark10100080012-Aug-16
4Tina8700090024-Oct-16
5Steve1000005002-Feb-16
6Michelle1200006003-Dec-16
7Alex85000(null)17-Jan-16
8Jo115000120030-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;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSIONHIRE_DATE
7Alex85000(null)17-Jan-16
8Jo115000120030-Oct-16
1John9000010001-Jan-16
3Mark10100080012-Aug-16
6Michelle1200006003-Dec-16
2Sally950005005-Sep-16
5Steve1000005002-Feb-16
4Tina8700090024-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;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSIONHIRE_DATE
7Alex85000(null)17-Jan-16
4Tina8700090024-Oct-16
1John9000010001-Jan-16
2Sally950005005-Sep-16
5Steve1000005002-Feb-16
3Mark10100080012-Aug-16
8Jo115000120030-Oct-16
6Michelle1200006003-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;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSIONHIRE_DATE
6Michelle1200006003-Dec-16
8Jo115000120030-Oct-16
3Mark10100080012-Aug-16
5Steve1000005002-Feb-16
2Sally950005005-Sep-16
1John9000010001-Jan-16
4Tina8700090024-Oct-16
7Alex85000(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;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSIONHIRE_DATE
4Tina8700090024-Oct-16
5Steve1000005002-Feb-16
2Sally950005005-Sep-16
6Michelle1200006003-Dec-16
3Mark10100080012-Aug-16
1John9000010001-Jan-16
8Jo115000120030-Oct-16
7Alex85000 (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;
SALESPERSON_IDFIRST_NAMETOTAL_EARNINGS
7Alex(null)
6Michelle120600
8Jo116200
3Mark101800
5Steve100500
2Sally95500
1John91000
4Tina87900

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;
SALESPERSON_IDFIRST_NAMESALARY/52
6Michelle2307.692308
8Jo2211.538462
3Mark1942.307692
5Steve1923.076923
2Sally1826.923077
1John1730.769231
4Tina1673.076923
7Alex1634.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;
SALESPERSON_IDFIRST_NAMEFLOOR(SALARY/52)
6Michelle2307
8Jo2211
3Mark1942
5Steve1923
2Sally1826
1John1730
4Tina1673
7Alex1634

You can see that the results have been ordered by the function value.

 

Example 8 – ORDER BY with NULLS FIRST

This example shows how the NULLS FIRST keyword works.

SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY commission NULLS FIRST;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSION
7Alex85000(null)
2Sally95000500
5Steve100000500
6Michelle120000600
3Mark101000800
4Tina87000900
1John900001000
8Jo1150001200

As you can see, the NULL values are shown at the top of the list.

 

Example 9 – ORDER BY with NULLS LAST

This example shows how the NULLS LAST keyword works.

SELECT
salesperson_id,
first_name,
salary,
commission
FROM salesperson
ORDER BY commission NULLS LAST;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSION
2Sally95000500
5Steve100000500
6Michelle120000600
3Mark101000800
4Tina87000900
1John900001000
8Jo1150001200
7Alex85000(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;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSION
2Sally95000500
5Steve100000500
6Michelle120000600
3Mark101000800
4Tina87000900
1John900001000
8Jo1150001200
7Alex85000(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;
SALESPERSON_IDFIRST_NAMESALARYCOMMISSION
5Steve100000500
2Sally95000500
6Michelle120000600
3Mark101000800
4Tina87000900
1John900001000
8Jo1150001200
7Alex85000(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.

 

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!

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Get Your SQL Cheat Sheets Now: