Calculate the nth Largest Column Data in a Table

In this blog I shall demonstrate how to find the nth largest column data in a SQL Server table.
 
 Step 1: Create the table
 
 CREATE TABLE [dbo].[tblScoreCard](
 [ID] [int] Primary key,
 [Name] [varchar](50),
 [Score] [int])
 

 Step 2: Populate some data in table tblScorecard

 

 

 
 Step 3: Create a stored procedure
 
 Create procedure sp_GetNthLargest(@value int)
 as
 begin
  select distinct ts1.ID,ts1.Name, ts1.score from tblscorecard ts1 where
 @value-1 =(select count(distinct(ts2.score)) from tblscorecard ts2 where ts2.score>ts1.score)
 end
 
 This stored procedure accepts and integer input @value which specifies the nth highest value.
 
 The highlighted query makes use of the outer query in order to find the count of each score.
 
 For example if you want to find the 2nd largest score in the tblscorecard table, then pass 2 as the input parameter.
 
 exec sp_GetNthLargest 2
 

 Output