hanuvalluri

 Profile Answers by hanuvalluri 

• Apr 1st, 2011

 

1.top 2 salary in each dept
ans: select top 2 * from (select * from emp orderby sal desc)
                                      group by desc;
                                                

hanuvalluri

 Profile Answers by hanuvalluri 

• Apr 1st, 2011

 

 2.2nd maximum sal in each dept

Select * From EMP A Where
(2-1) = (Select Count(Distinct(B.sal)) From EMP B Where B.sal >A.sal)

3. 2nd minimum sal in each dept.

Select * From EMP A Where
(2-1) = (Select Count(Distinct(B.sal)) From EMP B Where B.sal <A.sal)

mkaleelmd

 Profile Answers by mkaleelmd 

• Apr 7th, 2011

 

Top 2 salary for each department
-------------------------------------------------
SELECT * FROM

            (SELECT
                  employee_id,department_id,salary
                 ,dense_rank() OVER ( partition by department_id ORDER BY salary desc) as ranking
             FROM employees) a
WHERE a.ranking <=2

dinesh.smhdr

 Profile Answers by dinesh.smhdr 

• Sep 13th, 2011

 

Code
SELECT empno,ename,sal FROM (SELECT empno,ename,sal FROM emp ORDER BY sal DESC) WHERE ROWNUM <=2;


 

Barry Townsend

 Profile Answers by Barry Townsend 

• Sep 20th, 2011

 

max
(sal)

smonroy

 Profile Answers by smonroy 

• Sep 23rd, 2011

 

Ok, you have two easy options to each problem, you choose the one that suits you

Maximium salary

Code
1 OPTION


 


Declare @A  int, @B  Int


SET @A=(SELECT Max(sal) FROM Salary)


SET @B=(SELECT Max(sal) FROM Salary WHERE sal<>@A)


SELECT @A, @B


 


2 OPTION


 


SELECT a=(SELECT max(sal) FROM Salary), 


b=(SELECT max(sal) FROM Salary 


WHERE sal <>(SELECT max(sal) FROM Salary)) 

Minimum salary

Code
1 OPTION


Declare @A  int, @B  Int


Set @A=(Select min(sal) from Salary)


Set @B=(Select min(sal) from Salary where sal<>@A)


Select @A, @B


 


2 OPTION


select a=(select min(sal) from Salary), 


b=(select min(sal) from Salary 


where sal<>(select min(sal) from Salary)) 


 


 

ashadevi21

 Profile Answers by ashadevi21 

• Sep 27th, 2011

 

The Same can be modified and can be used for 2nd maximum and 2nd minimum as well

For 2nd Maximum
===========
SELECT dept_id, salary FROM
(SELECT
dept_id, salary
,DENSE_RANK() OVER ( PARTITION BY dept_id ORDER BY salary DESC) AS ranking
FROM EMPLOYEE ) a
WHERE a.ranking =2

For 2nd Minimum
==========
SELECT dept_id, salary FROM
(SELECT
dept_id, salary
,DENSE_RANK() OVER ( PARTITION BY dept_id ORDER BY salary ) AS ranking
FROM EMPLOYEE ) a
WHERE a.ranking =2

Prasanth

• Sep 12th, 2022

 

SELECT d.department_id, d.salary, d.employee_id FROM
(SELECT employee_id, department_id, salary, dense_rank() over(partition by department_id order by salary desc ) as salary_DenseRank from employees) d
WHERE d.salary_DenseRank =2;