Need explanation regarding the working of a self-join query on same column

Question

Question is to find the 2nd highest salary of the emp table using self join.

The code is as below :

SELECT DISTINCT sal FROM emp e1 
WHERE 2 = (SELECT count(DISTINCT sal) FROM emp e2 WHERE e1.sal <= e2.sal);

Can someone please explain the mechanism behind the working of this query -?

Answer 1

You can consider the following query with correlated subqueries as clarifying your question

select ( select max(distinct e2.sal) from emp e2 where e1.sal <= e2.sal ) as max_salary,
       ( select min(distinct e2.sal) from emp e2 where e1.sal <= e2.sal ) as min_salary,
       ( select count(distinct sal)  from emp e2 where e1.sal <= e2.sal ) 
     as salaries_count
  from emp e1
 order by salaries_count;

MAX_SALARY  MIN_SALARY  SALARIES_COUNT
5000        5000        1
5000        3000        2
5000        3000        2
5000        2975        3
5000        2850        4
5000        2450        5
5000        1600        6
5000        1500        7
5000        1300        8
5000        1250        9
5000        1250        9
5000        1100       10
5000         950       11
5000         800       12

distinct number of salaries( reversely_exceeding_salaries"distinct number of salaries less than the max salary -> 5000" ) remaining less than the maximum salary raises as the salary decreases.

There are two salaries_count with count value two for ( select count(distinct sal) from emp e2 where e1.sal <= e2.sal ) subquery which gives the second highest salary.