Finding the Nth highest value in a table

I recently was asked by a colleague about finding the Nth highest value in a table. For example

if there is an employee table then finding the 10th highest salary or something similar.

I thought about it and created a query below which obviously is not showing anyone’s salary 🙂 but shows how we can implement it.

Enjoy! and please comment.

declare @nth smallint

set @nth = 4

if (@nth <= 0)
begin
— default to 1 if inappropriate value is provided
set @nth = 1
end

set nocount on;

declare @t table (Id int identity(1, 1), Value decimal(18, 3))
insert into @t (Value) values (1234.56)
insert into @t (Value) values (5678.90)
insert into @t (Value) values (2345.67)
insert into @t (Value) values (3456.78)
insert into @t (Value) values (6789.10)
insert into @t (Value) values (7890.12)
insert into @t (Value) values (8912.34)
insert into @t (Value) values (4567.89)

select [Value] = max([t1].[Value])
from @t [t1]
left join (select top (@nth – 1) [Value]
from @t
order by value desc) [t2] on [t2].[Value] = [t1].[Value]
where [t2].[Value] is null

select [OrderId] = ROW_NUMBER() over (order by value desc)
, [Value] = value
from @t

set nocount off