Top Nth row
-
hi
i want to get Nth largest salary from this table? yes i mean 4th largest or 15 largest.
id salary
1 2000
2 3000
3 1000
4 10000
5 15000
thanx in advance
-
Hi,
there are various ways in which you could do this and I'm sure you'll get some other posts with other solutions but here's a one of ways that come to mind.
Dynamic SQL:
declare @n as int
declare @cmd as nvarchar(4000)
set @n = 20
set @cmd = 'select top ' + convert(varchar(12),@n) + ' * from mytable order by salary desc'
exec sp_executesql @cmd
Hope that helps
-
Hi!!!
I just modify Karl Grambow little bit..........
CREATE proc Top_NthSal
@TOP_ID int
as
declare @cmd as varchar(4000)
create table #test(Top_ID int IDENTITY (1, 1) NOT NULL , ID int ,Salary Int)
set @cmd = 'insert into #test (ID,salary) select * from Salary_Table order by salary desc'
exec (@cmd)
select ID,Salary from #test where TOP_ID = @TOP_ID
DROP table #test
Regards,
Papillon
-
Papillon, you don't have to use dynamic sql in your solution...
Performance could be a problem in Papillon's solution if the table is large. On the other hand, it is preferable to my suggestion below, if you need more than one "n'th salary" at a time.
declare @n int
declare @salary int
select @n = 2
declare @table table(id int, salary int)
insert @table
select 1, 2000 union all
select 2, 3000 union all
select 3, 1000 union all
select 4, 10000 union all
select 5, 15000
set rowcount @n
select @salary = salary from @table order by salary desc
set rowcount 0
select @salary
-
Can we do this thing without Top command?
-
Hi....
create proc Top_NthSal
@TOP_ID int
AS
create table #test(Top_ID int IDENTITY (1, 1) NOT NULL , ID int ,Salary Int)
insert into #test (ID,salary) select * from salary_table order by salary desc
if exists (select 1 from #test having count(salary) > 1)
begin
select * from salary_table
where salary = (select salary from #test where top_id = @top_id)
end
else
select ID , Salary from #test where TOP_ID = @TOP_ID
DROP table #test
Regards,
Papillon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply