How to select top five or 'N' rows in Oracle 11g
Selecting Top N Rows in Oracle 11g: A Comprehensive Guide

Learn various methods to efficiently retrieve the top N rows from a result set in Oracle 11g, addressing common challenges and syntax variations.
Selecting a limited number of rows, often referred to as 'top N' queries, is a common requirement in database applications. Whether you need the top 5 highest-paid employees, the 10 most recent orders, or simply a sample of data, Oracle 11g provides several powerful techniques to achieve this. Unlike some other database systems that offer a straightforward LIMIT
clause, Oracle 11g requires a slightly different approach, primarily leveraging analytic functions and subqueries. This article will explore the most effective methods, including ROWNUM
and analytic functions like ROW_NUMBER()
, RANK()
, and DENSE_RANK()
, to help you retrieve precisely the data you need.
Understanding ROWNUM for Top N Selection
The ROWNUM
pseudocolumn is a fundamental concept in Oracle for limiting the number of rows returned by a query. It assigns a sequential number to each row returned by a query, starting with 1. However, its behavior can be tricky, especially when combined with ORDER BY
clauses. ROWNUM
is assigned before the ORDER BY
clause is applied, meaning you cannot directly use ROWNUM
with an ORDER BY
in a single query to get the top N rows based on a specific sort order. To correctly use ROWNUM
for top N selection with ordering, you must first sort the data in an inner query and then apply the ROWNUM
filter in an outer query.
SELECT *
FROM (
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;
Using ROWNUM with an inline view to select the top 5 employees by salary.
ROWNUM
directly with ORDER BY
in a single SELECT
statement. This will not yield the desired result because ROWNUM
is assigned before the ORDER BY
clause is processed. Always use an inline view (subquery) to sort the data first, then filter by ROWNUM
.Leveraging Analytic Functions for Advanced Top N Queries
Analytic functions provide more flexible and powerful ways to handle top N queries, especially when dealing with ties or partitioning data. ROW_NUMBER()
, RANK()
, and DENSE_RANK()
are particularly useful for these scenarios. These functions assign a rank to each row within a partition of a result set, based on a specified order. The key difference lies in how they handle ties:
ROW_NUMBER()
: Assigns a unique, sequential number to each row within its partition, starting from 1. If there are ties, the order is arbitrary.RANK()
: Assigns the same rank to rows with identical values in the ordering column(s). The next rank after a tie will skip numbers (e.g., 1, 1, 3).DENSE_RANK()
: Similar toRANK()
, but it assigns consecutive ranks without gaps (e.g., 1, 1, 2).
SELECT employee_id, first_name, salary
FROM (
SELECT
employee_id, first_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
FROM employees
)
WHERE rn <= 5;
Using ROW_NUMBER() to select the top 5 employees by salary, handling ties arbitrarily.
SELECT employee_id, first_name, salary
FROM (
SELECT
employee_id, first_name, salary,
RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
)
WHERE rnk <= 5;
Using RANK() to select the top 5 employees by salary, including all tied rows.
SELECT employee_id, first_name, salary
FROM (
SELECT
employee_id, first_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as drnk
FROM employees
)
WHERE drnk <= 5;
Using DENSE_RANK() to select the top 5 employees by salary, including all tied rows without rank gaps.

Logical flow for Top N row selection in Oracle 11g
FETCH FIRST N ROWS ONLY
clause provides a much simpler and more intuitive syntax for top N queries, similar to LIMIT
in other databases. However, for Oracle 11g, the methods described here are essential.Selecting Top N Rows Per Group
A common variation of the top N problem is selecting the top N rows within each group. For example, you might want the top 3 highest-paid employees in each department. Analytic functions, particularly ROW_NUMBER()
with a PARTITION BY
clause, are perfectly suited for this task.
SELECT department_id, employee_id, first_name, salary
FROM (
SELECT
department_id, employee_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
FROM employees
)
WHERE rn <= 3;
Selecting the top 3 employees by salary within each department using ROW_NUMBER() with PARTITION BY.
This query first partitions the data by department_id
, then orders employees within each department by salary
in descending order, assigning a ROW_NUMBER()
to each. The outer query then filters for rows where rn
is less than or equal to 3, effectively giving you the top 3 employees per department.