Credits:
Description
Code
For SQL 2000 and later:
|
|
For SQL 2005 and later:
|
|
Example of all techniques:
|
Cool Tips > Data Searching >
Data Searching - Get nth Maximum RowCredits:Author: Unknown/ChillyDBA
Date: 2005/2012
DescriptionThere 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.
CodeFor SQL 2000 and later:
For SQL 2005 and later:
Example of all techniques:
|