Sql to find nth largest element in a column

There are lot ways to achieve this.
Few approaches…

Way 1 :
—————————————————————- first finding n maximum elements & then pick minimum among previous result.

select min(ElementName) from TableName where ElementName in (select distinct top n ElementName from TableName order by ElementName desc)

——————————–

Way 2 :
First finding n maximum elements & pick first in the result sorted in ascending order.
——————————–
select top 1 temp.ElementName from (select distinct top n ElementName from TableName order by ElementName desc) temp ordr by temp.ElementName asc
——————————–

What if TOP cannot be used?
With Row_Number, we can achieve.
Select elementName, Row_number (order by elementName desc) as row from TableName where row=n

———————————–
What if none of the predefined clauses or functions can b used?

Advertisements