SQL ORDER BY – The Complete Guide (2024)

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.

Example 1 – ORDER BY Column Name

This example orders by a single column name.

SELECTsalesperson_id,first_name,salary,commission,hire_dateFROM salespersonORDER BY first_name;

Result:

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.

SELECTsalesperson_id,first_name,salary,commission,hire_dateFROM salespersonORDER BY salary ASC;

Result:

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.

SELECTsalesperson_id,first_name,salary,commission,hire_dateFROM salespersonORDER BY salary DESC;

Result:

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.

SELECTsalesperson_id,first_name,salary,commission,hire_dateFROM salespersonORDER BY 2 DESC;

Result:

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).

SELECTsalesperson_id,first_name,salary + commission AS total_earningsFROM salespersonORDER BY total_earnings DESC;

Result:

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.

SELECTsalesperson_id,first_name,salary/52FROM salespersonORDER BY salary/52 DESC;

Result:

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.

SELECTsalesperson_id,first_name,FLOOR(salary/52)FROM salespersonORDER BY FLOOR(salary/52) DESC;

Result:

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.

SELECTsalesperson_id,first_name,salary,commissionFROM salespersonORDER BY commission NULLS FIRST;

Result:

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.

SELECTsalesperson_id,first_name,salary,commissionFROM salespersonORDER BY commission NULLS LAST;

Result:

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.

SELECTsalesperson_id,first_name,salary,commissionFROM salespersonORDER BY commission, salary;

Result:

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.

SELECTsalesperson_id,first_name,salary,commissionFROM salespersonORDER BY commission ASC, salary DESC;

Result:

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:

SELECTsalesperson_id,first_name,salary,commissionFROM salespersonORDER 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:

SELECTsalesperson_id,first_name,salary,commissionFROM salespersonORDER 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:

SELECTsalesperson_id,first_name,salary,commissionFROM salespersonORDER 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!