how to find nth highest salary
Answers were Sorted based on User's Feedback
Answer / rahul tripathi
The Genral answer which can work on any SQL:-
select salary from emp e1 where (n-1)=(select count(*)
from emp where salary > e1.salary )
Rahul Tripathi
rahultripathi@inkanpur.com
Is This Answer Correct ? | 249 Yes | 76 No |
Answer / brahma
select * from table_name t1 where (n-1)=
(select count(distinct(column_name)) from table_name t2
where t2.column_name>t1.column_name)
Is This Answer Correct ? | 101 Yes | 47 No |
Answer / kavitha.r
Sorry. This one is correct
select top 1 salary from employee where salary in (select
distinct
top n salary from employee order by salary desc) order by
salary asc
Is This Answer Correct ? | 44 Yes | 18 No |
select * from tablename.aliasname where nth=select
count(sal) from tablename where aliasname.sal<=sal
Is This Answer Correct ? | 38 Yes | 26 No |
Answer / nidhish
replace the N with wat ever number u want
select min(salary) from employee where salary in ( select
distinct top N salary from employee order by salary desc)
Is This Answer Correct ? | 26 Yes | 17 No |
Answer / amit
select * from emp as e1 where sal (n-1) = select distinct
(count(*) from emp as e2 where e2.sal>e1.sal)
Where n is no that u want which postion's salary u want
suppose second higest means n=2
Amit
Is This Answer Correct ? | 6 Yes | 0 No |
Answer / ayush sharma
select sal from (select distinct sal from emp order by desc)
where rownum <=n minus select sal from(select distinct sal
from emp order by desc) where rownum<=n-1;
Is This Answer Correct ? | 6 Yes | 1 No |
Answer / ramakrishna
select e1.* from emp e1 where n=(select
count(distinct(e2.sal)) from emp e2 where e1.sal<=e2.sal);
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / bharath
select distinct(sal) from emp a where 3 = (select
count(distinct(sal)) from emp b where a.sal<= b.sal)
replace 3 by 2 for second highest...
Is This Answer Correct ? | 4 Yes | 0 No |
OPTIMIZATION OF SP,CURSOR,TRIGGERS
What is difference between aggregate and analytic function?
Do you know what is a trace frag? Where do we use it?
what is hash table
WHAT IS UNIQUE IDENTIFIER DATA TYPE?
What command is used to rename the database?
What are extended events in sql server?
What are sparse columns?
Explain throw statement in sql server 2008?
how to take backup bcp out for a column in table in sql server?
what are file groups?
Name three version of sql server 2000 and also their differences?