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 …