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;

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;

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

Answer / swastik

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

Answer / swas

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

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);

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

Answer / arvind kumar

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

Answer / sureshramsing

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

Answer / suneelkumar

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

how to check the 3rd max salary from an employee table? One of the queries used is as follows: select sal from emp a where 3=(select count(distinct(sal)) from emp b where a.sal<=b.sal). Here in the sub query "select count(distinct(sal)) from emp b where a.sal<=b.sal" or "select count(distinct(sal)) from emp b where a.sal=b.sal" should reveal the same number of rows is in't it? Can any one here please explain me how is this query working perfectly. However, there is another query to get the 3rd highest of salaries of employees that logic I can understand. Pls find the query below. "select min(salary) from emp where salary in(select distinct top 3 salary from emp order by salary desc)" Please explain me how "select sal from emp a where 3=(select count(distinct(sal)) from emp b where a.sal<=b.sal)" works source:http://www.allinterview.com/showanswers/33264.html. Thanks in advance Regards, Karthik.

4 Answers  

what is log shipping? : Sql dba

0 Answers  

Can I call a procedure inside a function?

0 Answers  

What is latest version of sql?

0 Answers  

Which are the different types of indexes in sql?

0 Answers  

What are the types pl/sql code blocks?

0 Answers  

What does 0 mean in sql?

0 Answers  

IF EMP HAS 2 ROWS,DEPT HAS 4 ROWS.WHATS THE RESULT OF SELECT * FROM EMP,DEPT;

1 Answers  

what happens when the column is set to auto increment and you reach the maximum value for that table? : Sql dba

0 Answers  

How to perform a loop through all tables in pl/sql?

4 Answers   Evosys, MBT,

How do you pronounce sql?

0 Answers  

what is explain plan?

4 Answers