Cool Tips‎ > ‎Data Searching‎ > ‎

Data Searching - 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


 

ċ
Find Nth Max-Min Record.sql
(2k)
Andy Hughes,
Jun 15, 2012, 11:18 AM
v.3
Comments