SQL Server: Find 4,5 and 6 ranked entity

Find 4^th, 5^th and 6^th 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 4^th, 5^th and 6^th 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 Twitter |  Follow me on FaceBook