Query for second maximum salary in each in each department

Answers were Sorted based on User's Feedback



Query for second maximum salary in each in each department..

Answer / gopinath

select department_id, salary from
(select department_id, salary, rank() over(partition by
department_id order by salary desc) r from employees) where r=2;



cheers;

Is This Answer Correct ?    35 Yes 13 No

Query for second maximum salary in each in each department..

Answer / ram

SELECT empno,salary,dept_id FROM (SELECT empno,salary,dept_id,DENSE_RANK() OVER (PARTITION BY Dept_id ORDER BY Salary DESC)
as Rnk FROM emp) WHERE Rnk = 2;

Is This Answer Correct ?    8 Yes 0 No

Query for second maximum salary in each in each department..

Answer / manju

select deptno,Max(e1.sal) from emp e1
where e1.sal NOT IN(
select Max(e2.sal) from emp e2
group by e2.deptno
)
group by e1.deptno

Is This Answer Correct ?    13 Yes 10 No

Query for second maximum salary in each in each department..

Answer / swastik

SELECT *
FROM
    (
     SELECT Ename, Deptno, Sal,
     DENSE_RANK(PARTITION BY Deptno
                ORDER BY Sal DESC
                )TopRank
     FROM Emp
     )
WHERE TopRank = 2

Is This Answer Correct ?    0 Yes 0 No

Query for second maximum salary in each in each department..

Answer / swas

SELECT *
FROM
    (
     SELECT e1.*,
     DENSE_RANK() OVER(PARTITION BY Deptno
                ORDER BY Sal DESC
                )TopRank
     FROM Emp e1
     )
WHERE TopRank = 2 
/

Is This Answer Correct ?    0 Yes 0 No

Query for second maximum salary in each in each department..

Answer / akula

select * from emp e1 where 2 =(select count(distinct(sal)) from emp e2 where e1.dno=e2.dno and e1.sal<=e2.sal);

Is This Answer Correct ?    1 Yes 1 No

Query for second maximum salary in each in each department..

Answer / swaraj

Select Department,Max(Salary)
From (Select E1.Department,E1.Salary
From Employee E1,(Select Department,Max(Salary) as Salary
From Employee group by Department) E2
Where E1.Department = E2.Department
And E1.Salary<E2.Salary) E
Group by Department

Is This Answer Correct ?    0 Yes 0 No

Query for second maximum salary in each in each department..

Answer / arvind kumar

select * from employees e where (select count(salary) from
employees where salary >=e.salary)=2;

Is This Answer Correct ?    0 Yes 1 No

Query for second maximum salary in each in each department..

Answer / sureshramsing

select * from(select row_number() over(partition by deptno order by sal desc) rn,a.* from emp a) where rn=1;

Is This Answer Correct ?    0 Yes 1 No

Query for second maximum salary in each in each department..

Answer / suneelkumar

select * from emp x where 2=(select count(distinct sal) from
emp y where x.sal<=y.sal) group by deptno;

Is This Answer Correct ?    0 Yes 2 No

Post New Answer

More SQL PLSQL Interview Questions

What is difference between sql function and stored procedure?

0 Answers  


What are the advantages and disadvantages, compared to the standard SQL and SQL*plus ?

2 Answers  


What is the starting oracle error number? What is meant by forward declaration in functions?

0 Answers  


What is an intersect?

0 Answers  


What are the components of a PL/SQL block ?

3 Answers  






Can you join views in sql?

0 Answers  


what are the differences between get and post methods in form submitting. Give the case where we can use get and we can use post methods? : Sql dba

0 Answers  


What is sql architecture?

0 Answers  


What is trigger in pl sql with examples?

0 Answers  


What is set serveroutput on?

0 Answers  


What is function and procedure in pl sql?

0 Answers  


1. what is the exact use of hint in sql. 2. How we can avoid index scan in sql even though index is there in the table.

1 Answers   Polaris,


Categories