Can anyone tell me, How this query works?

Can anyone explain me how this query will work and find nth highest salary from salary table.

SELECT salary FROM salary_master s1 
WHERE (n-1) = (SELECT COUNT(*) FROM salary_master WHERE salary > s1.salary)

I mean, this query will iterate开发者_如何学C through all values?

Help me.

that will not find the nth highest salary, but this will:

SELECT salary
FROM salary_master
ORDER BY salary DESC
OFFSET n
LIMIT 1

No this will not iterate as such. This is still a normal query.

As you can see, it counts all salaries in the subquery that is larger than the current row entry.

So if you are the top earner, you should have no salaries greater than yours, so that is why the -1.

The subquery will count 'how many elements have bigger salary then the current element'. Then your main query checks if the current element in the table has n-1 elements that have bigger salary.

For example, for n = 5, the query will search for an element where 4 other elements have higher salary. Thus it will find the 5th highest.

This is not the best way to do this, though. Consider using ORDER BY and LIMIT ...