Q:

Oracle WHERE Clause/Searching

I'm a beginner to oracle.  In recent search I've seen WHERE N-1,3-2 ..so on. 
How does it work in searching data? 
This is my code attemtp so far:
SELECT name, salary 
FROM #Employee e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
             WHERE e2.salary > e1.salary)

A:

It's a classic( and pretty old ) SQL query to get nth highest salary.  I assume It is no longer used( I haven't seen ) in any production codes, but could be a favourite question among the interviewers.  
The N you are referring to is not a column or some unknown entity but a placeholder which should translate to a valid integer or bind argument in the working query.  It is a correlated subquery, a subquery that is evaluated once for each row processed by the outer query.  The way it works is that it takes a count of distinct list of salary values from employees that have a salary greater than each one of employees coming from the outer query and restricts the result where that count is equal to N-1.Which means you get those rows with nth highest salaries. 
A more commonly used way to do this would be to use analytic function dense_rank() ( or rank depending on your need ).  Read the documentation for these functions in case you aren't aware of them.  
SELECT first_name,
       salary
FROM (
     SELECT e.*,
            dense_rank() OVER(
                 ORDER BY salary desc
            ) rn
     FROM employees e
)
WHERE rn = 6; -- ( n = 6 )

In Oracle 12c and higher versions, even though the above query works, a handy option to use is the FETCH..FIRST syntax. 
SELECT *
FROM employees
ORDER BY salary DESC OFFSET 6 ROWS FETCH FIRST 1 ROWS WITH TIES; --n=6

