SQL Server: Find 4,5 and 6 ranked entity

Find 4th, 5th and 6th ranked entity

Let me first confess. This blog was never a planned effort. It just happened. How it happened…that’s equally interesting. Most of the time you might find questions to determine “Nth highest/lowest salary” of an employee. I was thinking on the similar lines but to determine the 4th, 5th and 6th highest/lowest ranked employee on the basis of their salary.

The first idea that followed was to create a CTE (Common Table Expression) and implement a SQL Ranking function. If incase you need a revision on either of them. Please follow the links below:

CTE: http://msdn.microsoft.com/en-us/library/ms190766.aspx

MS-SQL Ranking Functions: http://msdn.microsoft.com/en-us/library/ms189798.aspx

This should give you a wonderful idea about their applicability. Now continuing with the remaining of the post…

If you wish to calculate the lowest ranked order:

WITH employee_rank
AS (SELECT employee_key,
Row_number() OVER(ORDER BY employee_salary ASC) AS [Salary_Rank]       FROM tbl_employee

)
SELECT *
FROM employee_rank
WHERE [Salary_Rank] IN ( 4,
5, 6 )

If you wish to calculate the highest ranked order:

WITH employee_rank
AS (SELECT employee_key,
Row_number() OVER(ORDER BY employee_salary DESC) AS [Salary_Rank]       FROM tbl_employee

)
SELECT *
FROM employee_rank
WHERE [Salary_Rank] IN ( 4,
5, 6 )

That’s all folks. Even if you wish to calculate, say the Nth highest/lowest salary, you may use the SQL above with very minimal change. I avoid using MAX(), MIN() TOP and sub queries for such preliminary operations.

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *