FB

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!

5 thoughts on “SQL ORDER BY – The Complete Guide”

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents