N (th) Highest Salary

Question

I found a solution for finding n (th) top salaries from the internet:

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

But I can’t understand how the request is actually executed, i.e. how the request is processed at each step, especially in the subquery, where multiple aliases of the same table occur →

WHERE Emp2.Salary > Emp1.Salary

And what does the comparison operator do with this -

WHERE (N-1) = <subquery>

Can anyone help me with this?

Answer 1

Nth The highest salary is where there are higher N-1 salaries. For example. highest salary does not have higher salaries

Performing the following query change should demonstrate how this works:

SELECT * FROM Employee Emp1
CROSS APPLY
           (
           SELECT COUNT(DISTINCT(Emp2.Salary)) AS NHigher
           FROM Employee Emp2
           WHERE Emp2.Salary > Emp1.Salary
           ) X;

The script for Sql Server is here

, , RDBMS OFFSET LIMIT, N- ORDER BY.

. SQL Server 2012 2- :

SELECT *
FROM Employee Emp1
ORDER BY SALARY DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Answer 2

: , n (th) n (th)?   , n (th) n (th), 2   WHERE emp.Salary = WHERE emp.Salary >=: D

:

• Nth top pay

• ,

witch way u like:

, :

SELECT * FROM Employee as emp
WHERE emp.Salary = 
(
    SELECT TOP 1 t.Salary FROM 
    (
        SELECT TOP N(th) e.Salary FROM Employee as e
        Group by e.Salary
        Order By e.Salary
    ) as t
    ORDER BY t.Salary DESC
)

. DISTINCT:

SELECT * FROM Employee as emp
WHERE emp.Salary = 
(
    SELECT TOP 1 t.Salary FROM 
    (
        SELECT DISTINCT TOP N(th) e.Salary FROM Employee as e
        Order By e.Salary
    ) as t
    ORDER BY t.Salary DESC
)