calculate highest nth salary using sql

I want to calculate highest nth salary in Oracle. I've already done with my solution but on google I found one query doing the same thing.

Here is the query

SELECT *
FROM Employee Emp1
WHERE (N - 1) = (SELECT COUNT(DISTINCT(Emp2.orig_salary))
                    FROM Employee Emp2
                    WHERE emp2.orig_salary > emp1.orig_salary)

Data

   ID   Name    Birth       Orig_Salary
    2   John    15-JUL-97   2341    
    3   Joe     25-JAN-86   4321    
    4   Tom     13-SEP-06   2413    
    5   Jane    17-APR-05   7654    
    6   James   18-JUL-04   5679    
    7   Jodd    20-JUL-03   5438    
    8   Joke    01-JAN-02   8765    
    9   Jack    29-AUG-01   7896

I'm not not able to understand this query. After running inner query it will always gives me count of 8 after doing this it will go to where clause where it will pick salary which is higher than the outer query salary. How equal operator is working here in between inner and outer query and how comparison is happening.

Could any help me to understand how this query is working technically in back-end..?

Solution

SELECT *
FROM Employee Emp1
WHERE (N - 1) = (SELECT COUNT(DISTINCT(Emp2.orig_salary))
                FROM Employee Emp2  <--- cartesian product with same table
                WHERE emp2.orig_salary > emp1.orig_salary)  <---- but  do the cartesian product only if line of salary of emp 2 is greater than the current line of Emp1 'salary

e.g assume there are only 3 lines in the table:

ID Name Birth Orig_Salary

2   John    15-JUL-97   2341    
3   Joe     25-JAN-86   4321    
4   Tom     13-SEP-06   5413

the main query will look at the first line --> 2 John 15-JUL-97 2341 <---, and subquery will return 2 because the salaries 4321 (emp2.orig_salary) and 5413 (emp2.orig_salary) are greater than 2341 (emp1.orig_salary)

the main query will then look at the second line --> 3 Joe 25-JAN-86 4321 <---, and subquery will return 1 because the salaries 5413 (emp2.orig_salary) is greater than 2341 (emp1.orig_salary)

when i say subquery, it is the

=(SELECT COUNT(DISTINCT(Emp2.orig_salary))
                 FROM Employee Emp2  <--- cartesian product with same table
                WHERE emp2.orig_salary > emp1.orig_salary)

and the main query is

SELECT *
FROM Employee Emp1
WHERE

the returned value from the subquery is then compare to the where condition n-1, if the condition is satisfied, then it retrieves the line.