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 |
What is DAC? what is the use of it?
Why you need indexing? Where that is stored and what you mean by schema object? For what purpose we are using view?
How to add a new column to an existing table with "alter table ... Add" in ms sql server?
whats the maximum size of view state??
Can we insert data if clustered index is disabled?
What are statistics, under what circumstances they go out of date, how do you update them?
How get current date in SQL server 2000
4 Answers Cap Gemini, Polaris,
I have a table EMP in which the values will be like this EmpId Ename Sal DeptId 11 Ram 10000 10 11 Ram 10000 10 22 Raj 20000 20 22 Raj 20000 20 33 Anil 15000 30 33 Anil 15000 30 I want to delete only duplicate Rows. After Delete I want the output like this EmpId Ename Sal DeptId 11 Ram 10000 10 22 Raj 20000 20 33 Anil 15000 30
What is the difference between constraints and triggers?
What is an active database?
What will happen if a column containing char type data is changed to the nchar data type?
What are the types of indexes?