Oracle ORDER BY Clause

The entries in a table are not arranged in any particular order. Suppose, we want to see the data in the table arranged in ascending or descending order based on column, then we can use ORDER BY Clause.

Syntax:

The syntax of ORDER BY clause is as follows:

SELECT column_names 
FROM table_name
ORDER BY column_name [ASC | DESC] [NULLS FIRST | NULLS LAST]

Lets see a quick example using this table1:

Suppose, we want to arrange names in table alphabetically, i.e, in ascending order, then we use this query:

SELECT id, first_name, age 
FROM table1
ORDER BY first_name ASC;

Result:

The sort order is defined using these 2 operators after ORDER BY:

• ASC : Ascending Order ( If we don’t specify this, even then the rows in column arranged in ascending order by default)
• DESC : Descending Order

We also specify how we want to sort null values in rows, Using NULLS FIRST we can arrange all null values first and using NULLS LAST we can arrange them in last.

We can also sort data by using more than one column and have different sort order for each column.

ORDER BY Examples

We will be using the Employees Table from HR schema for this tutorial.

1. Sort rows by one column using ORDER BY

Suppose, we want to sort the table using first_name column and arrange all names alphabetically, then we use this query:

SELECT employee_id, first_name, last_name, salary 
FROM employees
ORDER BY first_name ASC;

Result:

Even if we don’t specify ASC the rows will arranged in ascending order by default.

Similarly, we can use DESC to arrange names in descending order, lets have a look:

SELECT employee_id, first_name, last_name, salary 
FROM employees
ORDER BY first_name DESC;

Result:

As we can see, all the first names are now arranged from Z to A.

2. Sort Rows by Multiple Columns using ORDER BY

We can also sort the data using Multiple columns. Lets have a look at one example:

SELECT employee_id, first_name, last_name, salary 
FROM employees
ORDER BY first_name ASC,last_name DESC ;

Result:

As we can see the query first sorts the first_name column in ascending order. When there are more than one matching first names, then it arranges the rows in second column in descending order.

In the last three rows the first name is David. In this case, our query sorts the data in last_name column in descending order.

3. Sorting and Grouping Rows using ORDER BY and GROUP BY

ORDER BY and GROUP BY are often used together in queries to sort and group the data.

Suppose we want to find out how many times the first name is repeated in table and arrange the data alphabetically, then we use this query:

SELECT first_name, COUNT(first_name) 
FROM employees
GROUP BY first_name
ORDER BY first_name;

Result:

This query gives us all the unique first names in the table and also how many times it is repeated in the table.

4. Sort NULL values in columns using ORDER BY clause

If the rows in table contain NULL values then we can sort them, using NULLS FIRST or NULLS LAST after ORDER BY clause.

Here’s an example to sort all the null values in column before other other values:

SELECT employee_id, first_name, last_name, manager_id 
FROM employees
ORDER BY manager_id ASC NULLS FIRST ;

Result:

As we can see, the manager ID contains only one null values which is shown first using NULLS FIRST.

5. Sort Rows in a column based on Date

We can also use ORDER BY to sort columns containing Date values. Lets have a look at this query:

SELECT employee_id, first_name, last_name, hire_date 
FROM employees
ORDER BY hire_date ;

Result:

The query sorts all the dates in hire_date column in ascending order.

6. Sort Rows in a column using function or expression

We can also use functions and expressions while applying ORDER BY clause on a column.

Suppose, we want to make the values in a column case-insensitive before sorting them in ascending or descending order, then we use this query:

SELECT employee_id, first_name, last_name, salary 
FROM employees
ORDER BY LOWER(first_name) ;

Result:

The lower function first coverts all the values in lower case and then the query returns all the values in first_name column alphabetically.

7. Sort Rows by Stating Position of Column

Instead of mentioning the column name explicitly we can also mention the column position in the ORDER BY query.

Lets have a look at the query:

SELECT employee_id, first_name, last_name, salary 
FROM employees
ORDER BY 2 ASC ;

Result:

The query returns all the values in first_name column which is at number 2 position in ascending order.