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