Finding the n th Highest value in SQL Server

Finding the n th Highest value in SQL Server:

In most of the SQL interviews, you will face this question frequently. So, I would like to write an article on this.

Scenario: Find the 6th highest ID in a table


Possible Solutions:

Am creating a table variable @VenkatTable,

declare @VenkatTable table(ID int)
Insert into @VenkatTable values(100)
Insert into @VenkatTable values(200)
Insert into @VenkatTable values(1400)
Insert into @VenkatTable values(2500)
Insert into @VenkatTable values(300)
Insert into @VenkatTable values(900)
Insert into @VenkatTable values(906)
Insert into @VenkatTable values(30000)
Insert into @VenkatTable values(12300)
Insert into @VenkatTable values(20000)

--USING GROUP BY STATEMENT:

First Option is by using group by operator. I have used SelfJoin to and fetch the count of records received and the count is equal to 6.

SELECT t1.ID FROM @VenkatTable t1 INNER JOIN @VenkatTable t2 ON t1.ID<=t2.ID GROUP BY t1.ID HAVING COUNT(t1.ID)=6

--USING TOP STATEMENT:

Second option is to use the Top operator. Get the Top 6 by desc and Fetch the first one by desc.

SELECT TOP 1 ID FROM ( SELECT TOP 6 ID FROM @VenkatTable ORDER BY ID DESC ) T ORDER BY ID ASC

--USING ROW_NUMBER

In SQL Server 2005, you can use row_number to fetch the record.

SELECT ID FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) T ,* FROM @VenkatTable)TEMP WHERE TEMP.T=6

--USING RANK

As an alternative to the row_number, we are having rank function to fetch the record.

SELECT ID FROM ( SELECT RANK() OVER (ORDER BY ID DESC) R,* FROM @VenkatTable)TEMP WHERE TEMP.R=6

-- USING DENSERANK

If the values are too close or same. In that case, we can opt DenseRank function.

SELECT ID FROM ( SELECT DENSE_RANK() OVER (ORDER BY ID DESC) R,* FROM @VenkatTable)TEMP WHERE TEMP.R=6

-- USING WHERE CONDITION

A normal select statement in a correlated query will provide you the output.

SELECT ID FROM @VenkatTable as V WHERE (SELECT COUNT (*) FROM @VenkatTable where ID>=v.ID)=6

Cheers,

Venkatesan Prabu .J