## Posts

Showing posts from February, 2011

### 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 num