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 |
How to create additional tablespaces for an new database?
Which of the following is not a database object TABLESPACE TABLE INDEX NONE
What is a tns file?
How many different types of indexes we have in oracle?
24 Answers IBM, TCS,
what is difference between foreign key and reference key
4. Display the order number and client number from the ORDER table. Output the result in the format. Client <clientno> ordered <orderno>
How do I start tns listener?
emp numb is unique because that is primary key,,but what is foreign key .. explain very clear with example
I have query like this. select dept_id, max_mark from stude_dept where min_mark= (select min(mini_mark) from stud_dept); How can i optimize this query. Can anyone help me with it
what is the difference between first normal form & second normal form?
What is a dynamic performance view in oracle?
SELECT THE RECORDS FROM 3 TABLES LIKE(T1,T2,T3) AND HOW CAN WE INSERT THAT RECORD IN ANOTHER TABLE LIKE(T4)?