ORDER BY & FETCH FIRST

Lesson 14 introduced ORDER BY and FETCH FIRST as part of the broader SELECT statement. This lesson goes deeper — covering multi-level sorting, expression-based sorting, how Oracle handles NULL values in sort order, the difference between ROWNUM and FETCH FIRST and why it matters, and the full pagination syntax used in real applications. All examples draw from the DataplexaStore and DataplexaHR schemas.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

ORDER BY in Depth

ORDER BY is the last clause Oracle evaluates in a query. Without it, Oracle makes no guarantee about the order rows are returned — the same query run twice can produce rows in a different sequence depending on how data is stored, which index is used, and whether the query runs in parallel. If order matters in the output, it must always be specified explicitly.

Each column in the ORDER BY list can independently be ASC (ascending, the default) or DESC (descending). The sort is applied left to right — the first column is the primary sort key, the second breaks ties within identical primary values, and so on.

-- Single column ascending — ASC is the default and can be omitted
SELECT product_name, category, unit_price
FROM   products
ORDER BY unit_price;

-- Single column descending
SELECT product_name, category, unit_price
FROM   products
ORDER BY unit_price DESC;

-- Multi-column sort — primary key first, secondary key breaks ties
-- Sort by category ascending, then by price descending within each category
SELECT category, product_name, unit_price
FROM   products
ORDER BY category ASC, unit_price DESC;

-- Each column can have its own direction independently
-- Hire date descending (most recent first), last name ascending within same date
SELECT first_name, last_name, hire_date, job_id
FROM   employees
ORDER BY hire_date DESC, last_name ASC;

• Without ORDER BY the result order is undefined — two identical queries can return rows in different orders on different executions; never rely on insertion order or index order to sort results
• The secondary sort only applies within groups of rows that share the same primary sort value — if all category values are distinct, unit_price DESC has no effect as a tiebreaker
• ASC is the default and can be omitted, but including it explicitly on the first column of a multi-column sort makes the intent obvious when reading the code

Sorting by Expressions and Aliases

ORDER BY is the only clause in a SELECT statement that can reference column aliases defined in the SELECT list. It is also the only clause where you can sort by an expression that does not appear as a named column — including computed values, function results, and CASE expressions.

-- Sort by a column alias defined in SELECT
SELECT product_name,
       unit_price * stock_qty  AS stock_value
FROM   products
ORDER BY stock_value DESC;

-- Sort by a computed expression not in the SELECT list
-- Sort products by the gap between their price and the average price
SELECT product_name, unit_price
FROM   products
ORDER BY ABS(unit_price - 100) ASC;

-- Sort by CASE expression — custom sort order not tied to alphabetical or numeric order
-- Priority sort: pending first, processing second, shipped third, everything else last
SELECT order_id, status, total_amount
FROM   orders
ORDER BY CASE status
             WHEN 'pending'    THEN 1
             WHEN 'processing' THEN 2
             WHEN 'shipped'    THEN 3
             ELSE                   4
         END,
         total_amount DESC;

-- Sort by a string function result
SELECT first_name, last_name
FROM   employees
ORDER BY SUBSTR(last_name, 1, 3) ASC,
         first_name              ASC;

• The CASE sort pattern is the correct way to impose a business-defined sort order that does not follow alphabetical or numeric sequence — it is far cleaner than storing a sort_order column in the table
• Sorting by an expression not in the SELECT list is valid in Oracle — the expression is evaluated for sorting purposes only and does not add a column to the result
• Column aliases can only be used in ORDER BY — referencing an alias in WHERE, GROUP BY, or HAVING raises ORA-00904: invalid identifier because those clauses execute before SELECT assigns the alias

NULL Handling in Sort Order

Oracle has defined default behaviour for NULL values in sorted output: in ascending order, NULLs appear last; in descending order, NULLs appear first. This is often unexpected — a descending sort intended to show the highest values first instead opens with a block of NULL rows.

NULLS FIRST and NULLS LAST override the default and can be applied independently to each column in the ORDER BY list.

-- Default NULL behaviour — NULLs last in ASC, first in DESC
SELECT employee_id, first_name, manager_id
FROM   employees
ORDER BY manager_id ASC;   -- NULLs appear at the end

SELECT employee_id, first_name, manager_id
FROM   employees
ORDER BY manager_id DESC;  -- NULLs appear at the start

-- NULLS LAST overrides the DESC default — real values first, NULLs pushed to end
SELECT employee_id, first_name, manager_id
FROM   employees
ORDER BY manager_id DESC NULLS LAST;

-- NULLS FIRST overrides the ASC default — NULLs promoted to the top
SELECT employee_id, first_name, manager_id
FROM   employees
ORDER BY manager_id ASC NULLS FIRST;

-- Practical use — sort orders by amount descending, unpaid (NULL amount) at the end
SELECT order_id, total_amount, status
FROM   orders
ORDER BY total_amount DESC NULLS LAST,
         order_id     ASC;

• The default NULL sort position surprises most developers on their first encounter with a DESC sort — always add NULLS LAST to descending sorts on nullable columns when you want real values to appear first
• NULLS FIRST and NULLS LAST apply per column — in a multi-column ORDER BY each column can have its own NULLS directive independently
• This behaviour is Oracle-specific — other databases handle NULL sort position differently; be aware of this when porting queries

ROWNUM vs FETCH FIRST

ROWNUM is Oracle's legacy row-limiting mechanism. It assigns a sequential number to each row as it is fetched from the table — before ORDER BY is applied. This creates a well-known trap: WHERE ROWNUM <= 5 on a query with ORDER BY fetches 5 arbitrary rows first and then sorts them, not the top 5 sorted rows.

The correct ROWNUM pattern requires a subquery: sort inside the subquery, then apply the ROWNUM filter in the outer query. FETCH FIRST, introduced in Oracle 12c, eliminates this entirely — it applies the limit after ORDER BY and is the correct, readable, modern approach.

-- ROWNUM trap — does NOT return the 5 most expensive products
-- ROWNUM filters first, ORDER BY sorts whatever 5 rows were grabbed
SELECT product_name, unit_price
FROM   products
WHERE  ROWNUM <= 5
ORDER BY unit_price DESC;

-- Correct ROWNUM pattern — sort in subquery, limit in outer query
SELECT product_name, unit_price
FROM   (SELECT product_name, unit_price
        FROM   products
        ORDER BY unit_price DESC)
WHERE  ROWNUM <= 5;

-- FETCH FIRST — correct, readable, no subquery needed (Oracle 12c+)
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC
FETCH FIRST 5 ROWS ONLY;

-- FETCH FIRST with PERCENT — top 20% of products by price
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC
FETCH FIRST 20 PERCENT ROWS ONLY;

-- WITH TIES — include all rows that tie on the last sort value
-- If two products share the 5th highest price, both are returned
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC
FETCH FIRST 5 ROWS WITH TIES;

• The ROWNUM trap is one of the most common Oracle mistakes — the results can look correct on small test datasets where the storage order happens to match the sort order, but break silently on production data
• FETCH FIRST requires Oracle 12c or later — on older versions use the subquery ROWNUM pattern; check your database version with SELECT * FROM v$version
• WITH TIES can return more rows than the specified limit — if 3 products are tied for 5th place and you ask for FETCH FIRST 5 ROWS WITH TIES, you get 7 rows; this is intentional and correct behaviour

Pagination with OFFSET and FETCH NEXT

Real applications rarely display all results at once. Pagination returns a fixed number of rows per page and allows the user to move through the full result set page by page. Oracle's OFFSET n ROWS FETCH NEXT m ROWS ONLY syntax handles this cleanly. OFFSET skips the first n rows after sorting; FETCH NEXT returns the following m.

For correct and consistent pagination, the ORDER BY must produce a deterministic sequence — if two rows could tie on the sort key, they might appear on different pages on different executions. Always include a unique column such as the primary key as the final sort key to break ties.

-- Page 1 — first 4 products by price descending
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC, product_id ASC  -- product_id breaks ties deterministically
FETCH FIRST 4 ROWS ONLY;

-- Page 2 — skip 4, return next 4
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC, product_id ASC
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

-- Page 3 — skip 8, return next 4
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC, product_id ASC
OFFSET 8 ROWS FETCH NEXT 4 ROWS ONLY;

-- Dynamic pagination formula: OFFSET (page_number - 1) * page_size ROWS
-- Page 3 with page_size = 4: OFFSET (3-1)*4 = 8
-- Useful in application code where page number is passed as a parameter

-- Paginating employees with a secondary sort for stability
SELECT employee_id, first_name, last_name, hire_date
FROM   employees
ORDER BY hire_date DESC, employee_id ASC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

• Always include the primary key as the last sort column in paginated queries — it guarantees every row has a unique position in the sort order and prevents the same row appearing on two different pages
• OFFSET 0 ROWS FETCH NEXT n ROWS ONLY is equivalent to FETCH FIRST n ROWS ONLY — both return the first page; the OFFSET 0 form is often used in application code so the offset can be parameterised uniformly
• Deep pagination — large OFFSET values — can be slow because Oracle must process and discard all skipped rows before returning the page; for very large datasets consider keyset pagination using a WHERE clause on the last seen value instead

Summary

Practice Questions

Quiz

Next up — Updating Data — Learn how to modify existing rows using UPDATE with single and multiple column assignments, subquery-based updates, and conditional updates with CASE.