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 |
explain what are the steps you will take, if you are tasked with securing an sql server? : Sql server database administration
What is Index ? Explain its Types?
What are the different index configurations a table can have?
What are indexes in ms sql server?
Explain what are partitioned views and distributed partitioned views?
You have several tables, and they are joined together for querying. The tables contain both clustered indexes and non clustered indexes to optimize performance, how should you distribute the tables and their indexes onto different file groups?
How to list all triggers in the database with sys.triggers in ms sql server?
write the query for taking database restore in sql?
What is “begin trans”, “commit tran”, “rollback tran” and “savetran”?
Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?
What is nolock?
i have a table #temp1(id, Name groupname ) and record like this 1 R1 S 2 R3 S 3 R2 S 4 R4 D 5 R5 D 6 R6 K 7 R7 K 8 R8 L 9 R9 L 10 R10 L 11 R11 K and i want to display record based on user defind sorting order e.g. 1 R4 D 2 R5 D 3 R6 K 4 R7 K 5 R11 K 6 R1 S 7 R3 S 8 R2 S 9 R8 L 10 R9 L 11 R10 L