Anybody can tell me, how do we find second largest emp
salary from emp table.. Thanks in advance ...
Answers were Sorted based on User's Feedback
Answer / raj
select max(sal) from emp where sal < (select max(sal) from
emp)
Is This Answer Correct ? | 31 Yes | 1 No |
Answer / selvaraj anna university coe
Oracle 10g Using : Find 2nd Highest Salary?
SELECT * FROM emp e WHERE 2=(SELECT COUNT(DISTINCT salary)
FROM emp x
WHERE x.salary >= e.salary);
Answer :
EMP_NO EMP_NAME SALARY
--------- ------------------------- ---------
107 SARABOOT 550000
Is This Answer Correct ? | 10 Yes | 1 No |
Answer / mahaboob
I would say that Raj has given the correct answer in general.
In most cases the interviewer restricts the parameter to be
written in the query as it might effect the performance.
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / amitkumar
Select TOP 1 salary from (select Distinct TOP 2 salary from
employee orderby salary desc)
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / samad
Dear
here i am sending new ans u can give the value in parameter
SELECT SAL,NO FROM(
SELECT SAL ,ROWNUM NO FROM
(SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC)) WHERE NO=:N
Regards,
Samad
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / hemalatha
Following is the query to find the nth maximum salary:
select * from emp
where sal=(select distinct(sal) from emp a
where &n=(select count(sal)from emp
where sal>=a.sal;))
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / keerthi
select(max(salary)
from employee
where sal>max salary&salary<max salary-1;
if you say no to my answer plz correct me.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / apsar
select sal from (select sal,count(sal) over order by salary desc c)temp where temp.c=&n;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / martin
select e.sal from emp e where &2>(select max(count{a.sal})
from emp a where a.sal > e.sal order by desc
)
Is This Answer Correct ? | 1 Yes | 4 No |
What is set operator oracle?
What happens if you use a wrong connect identifier?
What is a Tablespace?
What is Network Database link ?
what is Single Byte Overhead...?
What are inner join and outer join?
What is SAVE POINT ?
Is there a combination of "like" and "in" in sql?
How to create lov dynamically at runtime & attach to text field?
What are the system predefined user roles?
Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
DBMS vs. RDBMS