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]

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