Write a query to display maximum salary

Methods covered:

1. ORDER BY with ROWNUM
2. RANK analytic function
3. DENSE_RANK analytic function
4. FETCH FIRST
5. OFFSET and FETCH
6. Correlated subquery with COUNT
7. LATERAL subquery
8. ALL operator

Solution 1: Using ORDER BY and ROWNUM

This is a traditional Oracle method for finding the highest salary. The inner query sorts salaries from highest to lowest, and the outer query returns the first row.

SELECT sal
FROM (
    SELECT sal
    FROM emp
    ORDER BY sal DESC
)
WHERE ROWNUM = 1;

The highest salary appears first after sorting, so ROWNUM = 1 returns that salary.

Solution 2: Using RANK()

The RANK() analytic function assigns rank 1 to the highest salary. If multiple employees have the same highest salary, they all receive rank 1.

SELECT sal
FROM (
    SELECT sal,
           RANK() OVER (ORDER BY sal DESC) AS r
    FROM emp
)
WHERE r = 1;

Important:
RANK() can return multiple rows when multiple employees have the same highest salary.

Solution 3: Using DENSE_RANK()

Another common method is to use DENSE_RANK(). For the highest salary, both RANK and DENSE_RANK assign rank 1.

SELECT sal
FROM (
    SELECT sal,
           DENSE_RANK() OVER (ORDER BY sal DESC) AS r
    FROM emp
)
WHERE r = 1;

DENSE_RANK is especially useful when finding the first, second, third, or Nth distinct highest salary.

Solution 4: Using FETCH FIRST

The row limiting clause provides a simple and readable way to return the first row after sorting.

SELECT sal
FROM emp
ORDER BY sal DESC
FETCH FIRST 1 ROW ONLY;

Oracle sorts the salaries in descending order and returns only the first row.

Solution 5: Using OFFSET and FETCH

OFFSET can be used when you want to skip rows before returning a result.

SELECT sal
FROM emp
ORDER BY sal DESC
OFFSET (2 - 1) ROWS
FETCH FIRST 1 ROW ONLY;

Here, 2 - 1 = 1, so Oracle skips the first row and returns the second row in salary order. Therefore, this example is useful for finding the second row, not the highest salary.

For the highest salary, use:

SELECT sal
FROM emp
ORDER BY sal DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROW ONLY;

Note:
OFFSET works with rows, not necessarily distinct salary values. Duplicate salaries can affect Nth-highest-salary results.

Solution 6: Using a Correlated Subquery with COUNT()

This method compares each employee's salary with salaries in the same table.

SELECT *
FROM emp a
WHERE 1 = (
    SELECT COUNT(*)
    FROM emp b
    WHERE b.sal >= a.sal
);

For the highest-paid row, only one row has a salary greater than or equal to that salary when the highest salary occurs once.

Important:
This particular COUNT(*) approach assumes the highest salary occurs only once. If multiple employees share the highest salary, the count will be greater than 1 and the query may return no rows.

Solution 7: Using LATERAL

A LATERAL inline view can reference columns from a table that appears before it in the FROM clause.

SELECT *
FROM emp a,
LATERAL (
    SELECT *
    FROM (
        SELECT COUNT(*) AS c
        FROM emp b
        WHERE b.sal >= a.sal
    )
    WHERE c = 1
);

This applies similar counting logic to the correlated subquery method.

Like Solution 6, this version assumes that the highest salary occurs only once.

Solution 8: Using the ALL Operator

The ALL operator can also be used to compare a salary against values returned by a subquery.

SELECT sal
FROM emp
WHERE sal > ALL (
    SELECT sal - 1
    FROM emp
);

For numeric salaries, this condition can identify the maximum salary because the maximum salary is greater than every salary reduced by 1.

Interview Note:
Although this demonstrates the ALL operator, it is less clear than standard approaches such as MAX(), RANK(), DENSE_RANK(), or FETCH FIRST.

Comparison of the Methods

``` ```

Method	Purpose	Handles Ties?
ROWNUM	Returns first row after sorting	No
RANK()	Ranks salaries	Yes
DENSE_RANK()	Ranks distinct salary levels	Yes
FETCH FIRST	Modern Top-N syntax	Not with ROW ONLY
OFFSET + FETCH	Skip rows and fetch a specific row	Depends on query design
Correlated Subquery	Compares each row with other rows	Not in the shown COUNT(*) form
LATERAL	Per-row correlated calculation	Not in the shown COUNT(*) form
ALL	Compares against all subquery values	Can return tied maximum salaries

Simplest Method: Using MAX()

If you only need the highest salary value, the simplest solution is the MAX() aggregate function.

SELECT MAX(sal) AS highest_salary
FROM emp;

This directly returns the maximum salary from the EMP table.

Find Employees with the Highest Salary

If you want employee details instead of only the salary, use:

SELECT *
FROM emp
WHERE sal = (
    SELECT MAX(sal)
    FROM emp
);

This query also returns all employees if multiple employees share the highest salary.

Key Point:
Use MAX() when you only need the highest salary value. Use RANK() or DENSE_RANK() when ties matter, and use FETCH FIRST or ROWNUM when you simply need the first row after sorting.