3.3 Sorting Data with ORDER BY

3.3 Sorting Data with ORDER BY

Quick Answer: The ORDER BY clause controls the presentation order of rows in query results. Without an ORDER BY clause, the order of returned rows is non-deterministic (arbitrary). Rows can be sorted in ascending (ASC, default) or descending (DESC) order by column names, aliases, positional numbers, or expressions. By default, Oracle sorts NULL values last in ASC order and first in DESC order, which can be overridden with NULLS FIRST and NULLS LAST.

---

ORDER BY Clause Placement and Execution

The ORDER BY clause is always written as the last clause in a SELECT statement:

SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE department_id = 80
ORDER BY hire_date DESC;

Because ORDER BY executes after the SELECT list has been projected, it has unique capabilities compared to other clauses:

• It can reference column aliases defined in the SELECT clause.
• It can sort by expressions or columns not listed in the SELECT clause (provided DISTINCT or UNION is not used).

---

Sorting Options: ASC, DESC, and Collation

• ASC (Ascending): Default sort order.
  • Numeric: Lowest to highest (1, 2, 3...).
  • Date: Earliest to latest (01-JAN-20 before 01-JAN-21).
  • Character: Alphabetical (A to Z). In standard binary collation, all uppercase letters (A-Z) precede lowercase letters (a-z).
• DESC (Descending): Reverses the sort order (highest/latest/Z-A first).

-- Ascending order (ASC keyword is optional)
SELECT last_name, salary FROM employees ORDER BY salary ASC;

-- Descending order
SELECT last_name, salary FROM employees ORDER BY salary DESC;

---

Four Ways to Specify Sort Columns

Oracle SQL provides four ways to identify columns or expressions in the ORDER BY clause:

1. By Column Name

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

2. By Column Alias

Unlike the WHERE clause, the ORDER BY clause can reference column aliases because ORDER BY executes after SELECT.

SELECT employee_id, last_name, salary * 12 AS annual_sal
FROM employees
ORDER BY annual_sal DESC;

3. By Positional Index (1-based)

You can specify the numeric position of the column as it appears in the SELECT list (starting at 1).

-- Sorts by the 3rd column (salary) descending
SELECT employee_id, last_name, salary
FROM employees
ORDER BY 3 DESC;

[!WARNING] Positional sorting is valid in SQL, but referencing a number greater than the number of columns in the SELECT list produces an ORA-01785: ORDER BY item must be the number of a SELECT-list expression error.

4. By Expressions or Unselected Columns

You can sort by columns that are not included in the SELECT list:

-- Valid: hire_date is not in the SELECT list
SELECT employee_id, last_name, salary
FROM employees
ORDER BY hire_date;

[!IMPORTANT] The DISTINCT Restriction: If the SELECT statement uses the DISTINCT keyword, the ORDER BY clause CANNOT reference columns or expressions that do not appear in the SELECT list. Doing so triggers ORA-01791: not a SELECTed expression.

---

Multi-Column Sorting

You can sort by multiple columns by separating them with commas. Oracle sorts by the first (primary) column, and resolves ties using the second (secondary) column, and so on.

SELECT department_id, last_name, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

In this example:

1. All rows are grouped and ordered by department_id in ascending order.
2. Within each department_id, rows are ordered by salary from highest to lowest.
3. Notice that ASC or DESC applies only to the column immediately preceding it.

---

Oracle NULL Sorting Behavior

In Oracle SQL, NULL values are treated as the highest possible value for sorting comparisons.

Default Behavior:

• ASC sort: NULL values appear LAST (NULLS LAST).
• DESC sort: NULL values appear FIRST (NULLS FIRST).

-- Commission NULLs appear at the very bottom
SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY commission_pct ASC;

-- Commission NULLs appear at the very top
SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC;

Overriding with NULLS FIRST and NULLS LAST

You can explicitly control where nulls appear regardless of sort direction using NULLS FIRST or NULLS LAST:

-- Ascending sort, but push NULLs to the top
SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY commission_pct ASC NULLS FIRST;

-- Descending sort, but push NULLs to the bottom
SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST;

---

The Row Limiting Clause (OFFSET & FETCH)

Oracle Database supports the ANSI standard Row Limiting clause (OFFSET ... FETCH), simplifying pagination without requiring legacy ROWNUM subqueries.

Syntax Structure

[ OFFSET offset_rows { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ num_rows | percent PERCENT ] { ROW | ROWS } { ONLY | WITH TIES } ]

Key Row Limiting Patterns

1. Top-N Query (FETCH FIRST n ROWS ONLY)

-- Retrieve top 5 highest earners
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

2. Handling Ties (WITH TIES)

When multiple rows share the same sort values as the N-th row, WITH TIES returns all tying rows (requiring an ORDER BY clause):

-- If 5th and 6th employees both earn 12,000, both are returned (total 6 rows)
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

3. Pagination with OFFSET

-- Skip the first 10 rows and fetch the next 5 rows (rows 11-15)
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

4. Fetch by Percentage

-- Fetch top 10% of employee records
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;