CODEWITHSUNDEEP
sql
Jimit
Jimit

Reputation: 2259

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 through all values?

Help me.

Upvotes: 0

Views: 127

Answers (3)

Dan D.
Dan D.

Reputation: 74685

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

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

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166626

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.

Upvotes: 2

Konerak
Konerak

Reputation: 39773

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 ...

Upvotes: 1

Related Questions