How to find out department wise second maximum salary.

Showing Answers 1 - 27 of 27 Answers

Irfan

  • Mar 30th, 2006
 

Code
  1. SELECT deptno,

  2.        sal,

  3.        dr

  4. FROM

  5.   (SELECT deptno,

  6.           sal,

  7.           dense_rank()over(partition BY deptno

  8.                            ORDER BY sal DESC) dr

  9.    FROM emp)

  10. WHERE dr = 2

  11.  

  12.  

  Was this answer useful?  Yes

ardsouza

  • May 16th, 2006
 

Code
  1. SELECT DISTINCT d1.deptno,

  2.                 e1.sal

  3. FROM emp e1,

  4.      dept d1

  5. WHERE 2 =

  6.     (SELECT count(DISTINCT e2.sal)

  7.      FROM emp e2,

  8.           dept d2

  9.      WHERE e2.sal >= e1.sal

  10.        AND d2.deptno = e2.deptno

  11.        AND d1.deptno = d2.deptno)

  12.   AND d1.deptno = e1.deptno

  Was this answer useful?  Yes

Bhai

  • Jun 9th, 2006
 

Code
  1. SELECT max(e1.sal),

  2.        e1.deptno

  3. FROM emp e1

  4. WHERE sal <

  5.     (SELECT max(sal)

  6.      FROM emp e2

  7.      WHERE e2.deptno = e1.deptno)

  8. GROUP BY e1.deptno

  9.  

Code
  1. SELECT *

  2. FROM

  3.   (SELECT ename ,

  4.           sal,

  5.           deptno ,

  6.           rank() over (PARTITION BY deptno

  7.                        ORDER BY sal DESC) max_sal

  8.    FROM emp)

  9. WHERE max_sal= 2;

  10.  

  11.  

  Was this answer useful?  Yes

Code
  1. SELECT salary,

  2.        deptid

  3. FROM emp e1

  4. WHERE 2=

  5.     (SELECT count(DISTINCT(salary))

  6.      FROM emp e2

  7.      WHERE e1.salary<=e2.salary

  8.      GROUP BY deptid)

  9.  

  10.  

group by deptid
Assuming table is emp(eid,deptid,salary)

  Was this answer useful?  Yes

agssiva

  • Mar 25th, 2010
 

Code
  1. SELECT*

  2. FROM Emptbl e

  3. INNER JOIN

  4.   (SELECT Dep,

  5.           MAX(salary) AS sal

  6.    FROM Emptbl

  7.    GROUP BY Dep) AS t ON t.Dep = e.Dep

  8. WHERE e.Salary < t.sal

  9.  

  10.  

  Was this answer useful?  Yes

Table Name: employee
columns: employee,salary, dept

Code
  1. SELECT MAX (e1.salary) , e1.dept

  2. FROM employee e1

  3. WHERE e1.salary<

  4.     (SELECT MAX (e2.salary)

  5.      FROM employee e2

  6.      WHERE e1.dept= e2.dept

  7.      GROUP BY e2.dept)

  8. GROUP BY e1.dep

  9.  

  10.  

  Was this answer useful?  Yes

Code
  1. SELECT *

  2. FROM emp e,

  3.   (SELECT dept,

  4.           MAX(salary) AS salary

  5.    FROM emp

  6.    GROUP BY dept) e1

  7. WHERE e1.dept=e.dept

  8.   AND e.salary=e1.salary

  Was this answer useful?  Yes

sagar

  • Sep 29th, 2011
 

It's wrong.better to use dense_rank() function instead of rank() function

  Was this answer useful?  Yes

Mohan Rao

  • Nov 3rd, 2011
 

Code
  1. SELECT max(sal) ,

  2.        deptno

  3. FROM emp e1 ,

  4.   (SELECT max(sal) e2sal ,

  5.           deptno e2dept

  6.    FROM emp

  7.    GROUP BY deptno) e2

  8. WHERE e2.e2dept = e1.dept

  9.   AND e1.sal < e2.e2sal

  10. GROUP BY e1.deptno

  11.  

  12.  

  Was this answer useful?  Yes

sharika

  • Nov 24th, 2011
 

Code
  1. SELECT LEVEL,max(salary)

  2. FROM employee

  3. WHERE LEVEL=&LEVEL NO CONNECT BY

  4.   PRIOR salary>salary

  5. GROUP BY LEVEL;

  6.  

  7.  

  Was this answer useful?  Yes

srikanth

  • Jan 11th, 2012
 

Code
  1. SELECT deptno,

  2.        max(salary)

  3. FROM emp

  4. WHERE salary NOT IN

  5.     (SELECT max(salary)

  6.      FROM emp

  7.      GROUP BY deptno)

  8. GROUP BY deptno;

  9.  

  10.  


  Was this answer useful?  Yes

SASI

  • Apr 7th, 2012
 

Code
  1. SELECT EMPNO,

  2.        ENAME,

  3.        DEPTNO,

  4.        SAL

  5. FROM EMP E1

  6. WHERE 1 =

  7.     (SELECT COUNT(DISTINCT(SAL))

  8.      FROM EMP E2

  9.      WHERE E1.SAL <= E2.SAL)

  10.  

  11.  

  Was this answer useful?  Yes

Ashok

  • May 4th, 2012
 

What is the length of column in oracle?

  Was this answer useful?  Yes

smily08

  • Aug 30th, 2012
 

select max(salary) from EMP
WHERE SALARY < (SELECT MAX(SALARY) FROM EMP where dept = Dept1) and dept = Dept1

  Was this answer useful?  Yes

Nazeera Jaffar

  • Sep 25th, 2012
 

Code
  1. SELECT min(salary)

  2. FROM dept

  3. WHERE salary IN ( SELECT DISTINCT top 2 salary

  4.                         FROM dept

  5.                         ORDER BY salary DESC)

  Was this answer useful?  Yes

Mrinal

  • Dec 14th, 2012
 

Code
  1. SELECT DISTINCT d1.depno,

  2.                 e1.salary,e1.empid,d1.location

  3. FROM EMPLOYEE1 e1,

  4.      dept d1

  5. WHERE 2 =

  6.     (SELECT count(DISTINCT e2.salary)

  7.      FROM EMPLOYEE1 e2,

  8.           dept d2

  9.      WHERE e2.salary >= e1.salary

  10.        AND d2.depno = e2.Dep_No

  11.        AND d1.depno = d2.depno)

  12.   AND d1.depno = e1.Dep_No

  Was this answer useful?  Yes

gd_naidu

  • Nov 8th, 2014
 

Tested code

Code
  1. SELECT * FROM  (SELECT emp.*, DENSE_RANK()

  2. over (PARTITION BY deptno

  3. ORDER BY sal DESC) scmax_sal FROM emp) WHERE scmax_sal IN 2;

  Was this answer useful?  Yes

Dasish

  • Dec 12th, 2014
 

Select a.empno, a.deptno, a.sal from emp a, emp b where a.sal <= b.sal group by a.empno, a.deptno, a.sal having count( distinct b.sal) = ∈

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions