In Oracle, unlike databases like MySQL or PostgreSQL that support LIMIT, selecting the Top N rows requires different techniques depending on your Oracle version (especially before and after 12c).

This tutorial focuses on Oracle SQL, especially how to retrieve the Top 5 rows from a table.

---

1. Using ROWNUM (For Oracle 11g and earlier)

ROWNUM is a pseudo-column in Oracle that assigns a number to each returned row before ordering.

Example:

SELECT *
FROM employees
WHERE ROWNUM <= 5;

Important Limitation:
This query fetches any 5 arbitrary rows, not the top 5 highest or lowest based on any column.

---

2. Getting Top 5 by Order (Using ROWNUM + Subquery)

If you want the top 5 salaries, you must first sort using a subquery.

Example:

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

Explanation:

• Inner query: sorts all employees by salary descending.
• Outer query: picks the first 5 rows from the sorted result.

---

3. Using FETCH FIRST N ROWS ONLY (Oracle 12c and Later)

Oracle 12c introduced ANSI SQL-compliant FETCH clause, which is cleaner and more readable.

Example:

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

Notes:

• Easy to read and maintain.
• Works with pagination (see section below).
• More efficient than ROWNUM method in most cases.

---

4. Pagination with OFFSET and FETCH (Oracle 12c+)

To implement pagination (like Top 5, Next 5, etc.):

Example: Get rows 6 to 10

SELECT *
FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

---

5. Using ROW_NUMBER() (For More Control)

You can use ROW_NUMBER() when you need to select Top-N records per group or need more flexibility.

Example:

SELECT *
FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees e
)
WHERE rn <= 5;

This gives the same result as the previous top-5 queries, but it’s more useful when you want to:

• Select top N per department
• Apply complex filters
• Create a CTE

---

6. Top 5 Records Per Group (Advanced Use Case)

Let’s say you want the Top 5 earners per department:

SELECT *
FROM (
  SELECT e.*, 
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees e
)
WHERE rn <= 5;

This returns the top 5 employees (based on salary) for each department.

---

Summary of Methods

Method	Oracle Version	Use Case
ROWNUM <= 5	All versions	Any 5 rows (no order)
Subquery + ROWNUM	All versions	Top-N by ordering
FETCH FIRST 5 ROWS ONLY	Oracle 12c+	Clean syntax for Top-N queries
OFFSET ... FETCH NEXT	Oracle 12c+	Pagination (e.g. 6–10, 11–15, etc.)
ROW_NUMBER() with filtering	Oracle 10g+	More control, Top-N per group

---

Example Table and Output

Assume a sample employees table:

ID | NAME    | SALARY | DEPARTMENT_ID
---+---------+--------+---------------
 1 | Alice   | 8000   | 10
 2 | Bob     | 12000  | 20
 3 | Charlie | 6000   | 10
 4 | Dave    | 15000  | 20
 5 | Eva     | 10000  | 10
 6 | Frank   | 9000   | 20

Query:

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

Output:

ID	NAME	SALARY	DEPARTMENT_ID
4	Dave	15000	20
2	Bob	12000	20
5	Eva	10000	10
6	Frank	9000	20
1	Alice	8000	10

---

Final Tips

• Always include ORDER BY to define what “Top 5” means.
• Use FETCH FIRST if your Oracle version is 12c or higher.
• Avoid using ROWNUM alone when order matters.
• Use ROW_NUMBER() when doing advanced filtering like Top N per category.