ORDER BY Clause

The ORDER BY clause in Oracle SQL is used to sort the result set of a query. It allows you to sort the data based on one or more columns in either ascending or descending order. The ORDER BY clause is typically added at the end of the SELECT statement to organize the results in a meaningful way.

 

1. Syntax of the ORDER BY Clause

The basic syntax of the ORDER BY clause is:

SELECT column1, column2, ...

FROM table_name

WHERE condition

ORDER BY column_name [ASC|DESC], column_name [ASC|DESC], ...;

• column_name: The column(s) by which the result set will be sorted.
• ASC: Optional. Specifies the sort order as ascending. This is the default order if not specified.
• DESC: Optional. Specifies the sort order as descending.

You can order by one or more columns, and you can specify the sort direction (ASC or DESC) for each column.

 

2. Sorting in Ascending or Descending Order

• Ascending (ASC) Order: The default sorting order. It arranges the data from the smallest to the largest (alphabetically for strings, numerically for numbers, and chronologically for dates).
• Example: ORDER BY price ASC will sort the results with the lowest price first.
• Descending (DESC) Order: Sorts the data in reverse order, from largest to smallest.
• Example: ORDER BY price DESC will sort the results with the highest price first.

 

3. Sorting by Multiple Columns

You can sort the result set by multiple columns by specifying more than one column in the ORDER BY clause. Oracle will first sort the data based on the first column, then by the second column, and so on.

Example:

SELECT employee_name, department_id, salary

FROM employees

ORDER BY department_id ASC, salary DESC;

This query will:

1. Sort the data by department_id in ascending order.
2. Within each department, it will sort the employees by their salary in descending order.

 

4. Sorting with Column Position

Instead of specifying column names, you can use the position of the column in the SELECT statement to order the results.

Example:

SELECT employee_name, department_id, salary

FROM employees

ORDER BY 2 ASC, 3 DESC;

This query sorts the results:

• First by the second column (department_id) in ascending order.
• Then by the third column (salary) in descending order.

Note: Column position starts from 1 (not zero).

 

5. Sorting with Expressions and Functions

You can also use expressions or functions within the ORDER BY clause to control the sorting order. For instance, you can sort by the result of a function applied to a column.

Example:

SELECT employee_name, salary

FROM employees

ORDER BY UPPER(employee_name) ASC;

This query sorts the employee names in ascending order, case-insensitively, by applying the UPPER() function to each employee name.

You can also use arithmetic operations for sorting:

SELECT product_name, price, quantity

FROM products

ORDER BY price * quantity DESC;

This will sort the products based on the product of price and quantity in descending order.

 

6. Sorting by Date or Time

When sorting by a DATE or TIMESTAMP column, Oracle sorts the data chronologically. The format of the date/time does not matter, only the internal representation of the date.

Example:

SELECT employee_name, hire_date

FROM employees

ORDER BY hire_date DESC;

This query sorts employees by their hire date, with the most recently hired employees appearing first.

 

7. Sorting NULL Values

Oracle treats NULL values as the lowest possible value for sorting in ascending order and the highest value in descending order by default. However, you can modify this behavior using the NULLS FIRST or NULLS LAST keywords.

Example:

SELECT product_name, price

FROM products

ORDER BY price ASC NULLS LAST;

This query sorts the products in ascending order of price, but places NULL values at the end.

Alternatively:

SELECT product_name, price

FROM products

ORDER BY price DESC NULLS FIRST;

This query sorts the products in descending order of price, with NULL values appearing at the top.

 

8. Sorting with DISTINCT

When using the DISTINCT keyword, Oracle will first remove duplicate rows, and then apply the sorting as specified in the ORDER BY clause.

Example:

SELECT DISTINCT department_id

FROM employees

ORDER BY department_id ASC;

This query will return the unique department IDs and sort them in ascending order.

 

9. Sorting in GROUP BY Queries

You can also use the ORDER BY clause in queries that use GROUP BY to sort the grouped results.

Example:

SELECT department_id, COUNT(*) AS num_employees

FROM employees

GROUP BY department_id

ORDER BY num_employees DESC;

This query counts the number of employees in each department and sorts the departments by the count of employees in descending order.

 

10. Sorting in Subqueries

You can use the ORDER BY clause in subqueries, but it is typically not needed unless you are using LIMIT or FETCH FIRST. Oracle does not guarantee the order of rows unless explicitly instructed in the outer query.

Example:

SELECT employee_name

FROM (SELECT employee_name FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5;

This query first sorts the employees by salary in descending order and then returns the top 5 highest-paid employees.

 

11. Performance Considerations

• Indexing: If you're sorting by a column that is indexed, Oracle can use the index to speed up the sorting process. However, sorting large datasets still has a performance cost.
• Sorting Large Result Sets: Sorting large result sets can be time-consuming, especially if no indexes are used or if the data must be sorted in memory. Consider limiting the number of rows returned or optimizing queries when working with large datasets.
• ORDER BY with Aggregations: If you are using aggregate functions (e.g., COUNT(), SUM()) in your query, consider sorting the data after grouping.

 

12. Practical Use Cases of ORDER BY

• Sorting results alphabetically: Sorting employee names in alphabetical order.
• Sorting numerically: Sorting products by price to find the least or most expensive.
• Sorting by date: Sorting orders by the most recent order date.
• Paginated queries: When using ROWNUM or FETCH FIRST, the ORDER BY clause helps to return results in a defined order.

 

13. Conclusion

The ORDER BY clause is an essential part of Oracle SQL, allowing you to sort query results by one or more columns. It can be customized to sort data in both ascending and descending order, and it can handle complex sorting scenarios such as sorting by multiple columns, sorting by expressions, and dealing with NULL values. Understanding how to use the ORDER BY clause efficiently can significantly improve the readability and usefulness of your query results.