In SQL Server 2005, How to get 6th highest salary from a table?
Regards,
Prashanth Chenna.
Printable View
In SQL Server 2005, How to get 6th highest salary from a table?
Regards,
Prashanth Chenna.
There are already few posts for ur question. You can get your reply from here
[URL="http://http://www.geekinterview.com/talk/7870-nth-highest-salary.html"]http://http://www.geekinterview.com/talk/7870-nth-highest-salary.html[/URL]
---V V---
Vikas Vaidya
You can use the Query as fallowed
SELECT * FROM <TABLENAME> T1 WHERE n=
(SELECT COUNT(DISTINCT <COLUMNNAME>) FROM <TABLENAME> T2 WHERE T1.<COLUMNNAME> <=T1.<COLUMNNAME>)
where 'n' is the nth highest value for the <COLUMNNAME> in the <TABLENAME>.
select top 1 salary
from (select distinct top 6 salary from employee order by salary desc) a
order by salary
Hi jrameshvjr,
your query will give the only one record, if there are many number of employees holding the same salary then it won't be applicable..
select * from <tablename>emp where salary not in (select distinct top n-1 salary from <tablename>emp order by salary desc)
n=number of highest salary
select top 1 emp_salary from (select top 6 emp_salary from tblemployee order by emp_salary desc) a order by emp_salary asc
select top(6) from tablename,but this will not applicable where there more number of employees drawing the same salary? it includes duplicate rows