2012/03/23

Nth Maximum in SQL


DECLARE @empSalary table
(
    empid int not null primary key identity(1,1),
    name varchar(100),
    salary decimal(18,2)
)

declare @i int
set @i = 1
while @i <= 100
begin
    insert INTO @empSalary (name, salary)
    VALUES('emp ' + convert(nvarchar(10), @i), '100' + convert(nvarchar(10), @i))
    set @i = @i + 1
end


Declare @cnt int
Declare @total int
Set @cnt = 295
Select @cnt = max(empid) from @empSalary

IF @cnt <= @total
begin
    Select top 1 salary from @empSalary where salary in (Select top (@cnt) salary from @empSalary order by salary desc )
end
else
begin
    Select 'Not Found'
end

0 comments:

Post a Comment