Write a query to get 2nd maximum salary in an employee table ?
Answers were Sorted based on User's Feedback
Answer / m
select max(sal) from emp where sal<(select max(sal) from
emp))
Is This Answer Correct ? | 219 Yes | 39 No |
Answer / kotravel. b
SELECT MAX(SAL) FROM EMPLOYEE WHERE SAL<(SELECT MAX(SAL)
FROM EMPLOYEE);
THIS will surly give 2^nd max sal ok this correct ans ok....
Is This Answer Correct ? | 85 Yes | 13 No |
Answer / anil_abbireddy
select * from emp a where 2=(select count(distinct sal)
from emp b where a.sal<=b.sal)
Is This Answer Correct ? | 44 Yes | 12 No |
Answer / sohail
select empno,ename,sal
from (select ename,empno,sal,dense_rank()
over(order by sal desc)topn
from emp)
where topn=2
Is This Answer Correct ? | 69 Yes | 46 No |
Answer / anil_abbireddy
select * from emp a where 2=(select count(distinct sal)
from emp b where a.sal<=b.sal)
Is This Answer Correct ? | 30 Yes | 8 No |
Answer / venkat
select max(sal) from emp where sal<(select max(sal) from
emp);
Is This Answer Correct ? | 19 Yes | 5 No |
Answer / khandu shinde
select a.sal
from (
select sal ,rownum no
from (
select distinct sal
from emp
order by sal desc
)
) a
where a.no=2
Is This Answer Correct ? | 16 Yes | 6 No |
Answer / abhay
Select Max(Sal)
From Emp E
Where E.Sal < (Select Max(Sal) From Emp)
Is This Answer Correct ? | 9 Yes | 1 No |
Answer / uday
Hi friends i think it is surely work because i worked for this.
select * from emp e where 1=(select count(distinct d.esal)
from emp d where d.esal>e.esal)
any one plz try if it's not work
plz let me know.
uday_testing@yahoo.co.in
Is This Answer Correct ? | 7 Yes | 3 No |
Answer / wei
The best answer should be the first one, this is because
the sql statement accesses table emp only once.
Is This Answer Correct ? | 9 Yes | 6 No |
Can one improve the performance of sql*loader? : aql loader
Can dml statements be used in pl/sql?
What are the syntax and use of the coalesce function?
What has stored procedures in sql?
Can I copy :old and :new pseudo-records in/to an oracle stored procedure?
How do I order columns in sql?
Can we use ddl statements in stored procedure?
1) Synonyms 2) Co-related Subquery 3) Different Jobs in Plsql 4) Explain Plan 5) Wrap 6) Query Optimization Technique 7) Bulk Collect 8) Types of index 9) IF primary key is created then the index created ? 10) Foreign Key 11) Exception Handling 12) Difference Between Delete and Trunc 13) Procedure Overloading 14) Grant Revoke 15) Procedure Argument types. 16) Functions. 17) Joins
What is a trigger in pl/sql?
Can instead of triggers be used to fire once for each statement on a view?
what is the command line end user interface - mysql? : Sql dba
What is the use of partition by in sql?