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
Comments