Write a query to display top 5 rows

Methods covered:

1. ROW LIMITING clause
2. Inline view and ROWNUM
3. WITH clause and ROWNUM
4. RANK
5. DENSE_RANK
6. ROW_NUMBER

1. ROW LIMITING clause

Example:

SELECT *
FROM emp
ORDER BY sal DESC
FETCH FIRST 5 ROWS ONLY;

2. Inline view and ROWNUM

Example:

SELECT *
FROM (
    SELECT *
    FROM emp
    ORDER BY sal DESC
)
WHERE rownum <= 5;

3. WITH clause and ROWNUM

The WITH clause can be used to make the query easier to read.

Example:

WITH ds AS (
    SELECT *
    FROM emp
    ORDER BY sal DESC
)
SELECT *
FROM ds
WHERE rownum <= 5;

4. Using RANK function

RANK gives the same rank to equal values and leaves gaps in the ranking sequence.

Example:

SELECT *
FROM (
    SELECT emp.*,
           RANK() OVER (ORDER BY sal DESC) AS val_rank
    FROM emp
)
WHERE val_rank <= 5;

5. Using DENSE_RANK function

DENSE_RANK gives the same rank to equal values and does not leave gaps in the ranking sequence.

Example:

SELECT *
FROM (
    SELECT emp.*,
           DENSE_RANK() OVER (ORDER BY sal DESC) AS val_rank
    FROM emp
)
WHERE val_rank <= 5;

6. Using ROW_NUMBER function

ROW_NUMBER assigns a unique number to each row. It does not give the same number to duplicate values.

Example:

SELECT *
FROM (
    SELECT emp.*,
           ROW_NUMBER() OVER (ORDER BY sal DESC) AS val_rank
    FROM emp
)
WHERE val_rank <= 5;

Difference between the methods

Method	Behavior
ROW LIMITING clause	Simple and modern way to fetch top rows
ROWNUM with inline view	Classic Oracle approach for top-N queries
RANK	Returns ties with gaps in ranking
DENSE_RANK	Returns ties without gaps in ranking
ROW_NUMBER	Assigns a unique number to each row

When to use which method?

• Use FETCH FIRST for a simple top-N query.
• Use ROWNUM when working with older Oracle syntax.
• Use RANK when you want ties to share the same rank.
• Use DENSE_RANK when you want ties without gaps.
• Use ROW_NUMBER when you need exactly N rows.

Key Point:
For top salary queries, the row limiting clause is the cleanest option, while analytic functions are best when you need ranking logic and tie handling.