Calculating nth Max salary

This is a very common question in any interview on SQL. Max salary can be calculated in different ways. Following are the few examples and their explanation.

1. Using simple max function. (Retruns First Max Salary only)

SQL> SELECT MAX(sal)
           FROM emp;

 MAX(SAL)
----------
      5000

This is to find the first max salary from EMP table. MAX is a group function which returns maximum value out of group of records or all the records.

Click Here, to know more about MAX

2. Using subqueries.

SQL> SELECT MAX(sal)
           FROM emp
           WHERE sal < (SELECT MAX(sal) FROM emp);

  MAX(SAL)
----------
      3000

Look at the above query closely, sal < max(sal) returns all the salaries less than first max salary. Calculating maximum salary out of all the salaries less than the first maximum salary will return second maximum salary.

So use multiple subqueries to get nth max salary.

3. Using ROWNUM

ROWNUM is a psuedocolumn which retruns a number indicating the order in which Oracle selects the data.

SQL> SELECT Salary
           FROM (SELECT rownum MAXSALARY, sal Salary FROM ( SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC))
           WHERE MAXSALARY = 3;

    SALARY
----------
      2975

Change the MAXSALARY condition to nth value to get nth max salary.
Click Here, to know more about ROWNUM.


4. Using MIN() function along with ROWNUM to calculate nth maximum salary.

SQL> SELECT MIN(sal)  FROM emp WHERE sal IN (SELECT sal FROM (SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC) where rownum<=3)

  MIN(SAL)
----------
      2975

5. Using LEVEL

SQL> SELECT LEVEL, MAX(sal) FROM emp
WHERE LEVEL = 3
CONNECT BY PRIOR sal>sal
GROUP BY LEVEL;

     LEVEL   MAX(SAL)
---------- ----------
         3       2975

6. Using Co-Related Subqueries

SELECT * FROM emp r1
WHERE &n = (SELECT COUNT(DISTINCT(sal))
FROM emp WHERE sal >= r1.sal);