Introduction
It's a very simple trick to get rows on nth maximum number from a table. In this tip, I used common table expression (CTE) and DENSE_RANK()
function. To know more about CTE, click this link and for DENSE_RANK()
, click this link.
Using the Code
A simple code snippet is given below:
DECLARE @g_query AS VARCHAR(256)
DECLARE @g_nth_highest AS INT
DECLARE @g_nth_row AS INT
SET @g_nth_height = 7;
SET @g_nth_row = 3;
SET @g_query =
'WITH CTE_RESULT AS
(
SELECT ct_salary, DENSE_RANK() OVER
(
ORDER BY ct_salary DESC
) AS DENSERANK_RESULT FROM T_EMPLOYEE
)
SELECT TOP '+ CONVERT(VARCHAR, @g_nth_row) + _
' ct_salary AS SALARY FROM CTE_RESULT WHERE CTE_RESULT.DENSERANK_RESULT =' + _
CONVERT(VARCHAR, @g_nth_highest);
EXEC(@g_query)
Conclusion
I hope you guys get the scenario and this might be helpful to you. Enjoy!
History
- Saturday, December 22nd, 2015: Initial post