SQL Server Performance Forum – Threads Archive

nth maximum sal using rownum

dear all,
can we find out the nth maximum salary using row_number function?
or again we need to go for correlated subquery?
Thank you
Regards
Jai

---

is this want u need ? Declare @TestTable Table (EmpID int, Salary int)
insert into @TestTable select 101,1000
insert into @TestTable select 102,2000
insert into @TestTable select 103,3000
insert into @TestTable select 1,4000
insert into @TestTable select 2,5000
insert into @TestTable select 55,6000
insert into @TestTable select 66,7000
insert into @TestTable select 77,9000
insert into @TestTable select 88,10000
insert into @TestTable select 99,11000
insert into @TestTable select 22,12000
select *from
(SELECT ROW_NUMBER() OVER (ORDER BY Salary desc) as SequenceNo, *
FROM @TestTable ) a where sequenceno=9
madhu

---

If you care about the duplicates, then you should use DENSE_RANK() Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

or to compatible to both 2000 and 2005 Select min(salary) from (select top 9 salary from table order by salary DESC)T Also, you need to do Google search for some more methods Madhivanan Failing to plan is Planning to fail

---

]]>