Get nth Maximum Row
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown/ChillyDBA
Date: 2005/2012
Description
There is occasionally the need to retrieve the nth row from a record set sorted by a specific criteria. Before SQL 2005 and the introduction of the ROW_NUMBER() function, this usually involved complex and/or expensive correlated subqueries, or the storing/upkeep of preprocessed record # information.
To demonstrate a relatively straightforward way of achieving this, I have included 2 code samples each below showing how this can be achieved for SQL 2000+ and for SQL 2005+ (the technique for SQL 2000 is still valid for all versions of SQL Server).
The examples shown extract row with the 5th highest value of Risk_Num. The ordering criteria could be easily altered to achieve 5th lowest or other aggregation-based criteria.
Code
For SQL 2000 and later:
SELECT *
FROM <TableName>
WHERE <RankedRowName> =
(
SELECT MIN(<RankedRowName>)
FROM
(
SELECT DISTINCT TOP (<n>) <RankedRowName>
FROM <TableName>
ORDER BY <RankedRowName> DESC
) AS B
)
SELECT *
FROM
(
SELECT *,
(
SELECT COUNT(<RankedRowName>)
FROM <TableName>
WHERE <RankedRowName> >=T.<RankedRowName>
) AS Rank
FROM <TableName> AS T
) AS Z
WHERE Rank=<n>
For SQL 2005 and later:
SELECT
t.*
FROM
(
SELECT
e1.*,
ROW_NUMBER() OVER (ORDER BY e1.<RankedRowName> DESC) AS _Rank
FROM <TableName> AS e1
) AS t
WHERE t._Rank = <n>
SELECT *
FROM
(
SELECT *,
(DENSE_RANK() OVER (ORDER BY <RankedRowName> DESC)) AS Rank
FROM <TableName>
) AS Z
WHERE Rank=<n>
Example of all techniques:
CREATE TABLE #Risk (
[Col001] [varchar] (200) NULL ,
[Col002] [varchar] (200) NULL ,
[Col003] [varchar] (10) NULL,
[RowNum_Risk] [int] NOT NULL ,
CONSTRAINT [PK_risk] PRIMARY KEY CLUSTERED
(
[RowNum_Risk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO #Risk (Col001, Col002, Col003, RowNum_Risk)
SELECT 'AAA1','BBB1','CCC1',1
UNION
SELECT 'AAA2','BBB2','CCC2',2
UNION
SELECT 'AAA3','BBB3','CCC3',3
UNION
SELECT 'AAA4','BBB4','CCC4',4
UNION
SELECT 'AAA5','BBB5','CCC5',5
UNION
SELECT 'AAA6','BBB6','CCC6',6
UNION
SELECT 'AAA7','BBB7','CCC7',7
UNION
SELECT 'AAA8','BBB8','CCC8',8
UNION
SELECT 'AAA9','BBB9','CCC9',9
UNION
SELECT 'AAA10','BBB10','CCC10',10
DECLARE @RequiredRecordNumber INT
SELECT @RequiredRecordNumber = 5
-- SQL 2000+ Method #1
SELECT *
FROM #Risk
WHERE RowNum_Risk =
(
SELECT MIN(RowNum_Risk)
FROM
(
SELECT DISTINCT TOP (@RequiredRecordNumber) RowNum_Risk
FROM #Risk
ORDER BY RowNum_Risk DESC
) AS B
)
-- SQL 2000+ Method #2
SELECT *
FROM
(
SELECT *,
(
SELECT COUNT(RowNum_Risk)
FROM #Risk
WHERE RowNum_Risk>=T.RowNum_Risk
) AS Rank
FROM #Risk AS T
) AS Z
WHERE Rank=@RequiredRecordNumber
-- SQL 2005+ Method #1
SELECT
t.*
FROM
(
SELECT
e1.*,
ROW_NUMBER() OVER (ORDER BY e1.RowNum_Risk DESC) AS _Rank
FROM #Risk AS e1
) AS t
WHERE t._Rank = @RequiredRecordNumber
-- SQL 2005+ Method #2
SELECT *
FROM
(
SELECT *,
(Dense_Rank() OVER (ORDER BY RowNum_Risk DESC)) AS Rank
FROM #Risk) AS Z
WHERE Rank=@RequiredRecordNumber
DROP TABLE #Risk