SQL ORDER BY – The Complete Guide

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!