Find the Nth largest record - How it works?

Language agnostic approach:

Employees -Table

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        100 Steven                    24000 
        101 Neena                     17000 
        102 Lex                       17000 
        103 Alexander                  9000 
        104 Bruce                      6000 
        105 David                      4800 
        106 Valli                      4800 
        107 Diana                      4200 

The query here fetches the employee whose salary is 2nd largest from the above table.

select first_name, salary 
       from employees e1 
       where 1 =  (select COUNT(DISTINCT SALARY)
                          from employees e2 
                          where e2.salary > e1.salary); 

To understand the query we need to know how the sub-query works first. If this is known, the rest of the query is easy. The sub query is the one which looks for the result by taking the value of the main query as input.

The sub query job is to find out the no. of salaries which are greater than a given salary (which is inputted by the main query e1.salary)

No. of salaries which are greater than 24000

select COUNT(DISTINCT SALARY)
                          from employees e2 
                          where e2.salary > 24000  => returns 0

No. of salaries which are greater than 17000

select COUNT(DISTINCT SALARY)

                          from employees e2 
                          where e2.salary > 17000 => returns 1

To find the 2nd highest salary, the sub query should look for a salary where the no. of salaries which are greater than its salary is 1. 

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        101 Neena                     17000 
        102 Lex                       17000 

In Oracle:  

In Oracle, we use ROW_NUMBER() function to order the rows based on salary in descending order and use that row number for getting the nth largest salary.

The limitation of this approach is it fetches only 1 row, though there are 2 rows which match the given criteria. But, the query is easy to understand.

select employee_id, first_name, salary
       from (select e.*, 
                    ROW_NUMBER() OVER(ORDER BY salary DESC) rownumber 
                    from employees e) 
       where rownumber =2;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        101 Neena                     17000