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

Comments

Popular posts from this blog

ASP.NET Session States in SQL Server Mode

How to find client's MAC Address in Asp.Net and C#.net

Use XML Data For Save in Database