How to find out department wise second maximum salary.

Showing Answers 1 - 75 of 91 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

Ejaz Ahmed

  • Apr 18th, 2015
 

1. SELECT deptno, MAX(sal) "SAL"
2. FROM emp
3. WHERE (deptno,sal) NOT IN (SELECT deptno,MAX(sal)
4. FROM emp
5. GROUP BY deptno)
6. GROUP BY deptno

  Was this answer useful?  Yes

Bismaya

  • Sep 2nd, 2015
 

It wont work as it returns more than row in single row subquery.

  Was this answer useful?  Yes

Nitin Tomer

  • Nov 4th, 2016
 

Below is the data set:

SALARY DEPT
1000 q1
2000 q1
3000 q1
4000 q1
5000 q2
5000 q2
7000 q2
8000 q2
1000 q3
2000 q3
3000 q3
4000 q3
5000 q4
5000 q4
7000 q4
8000 q4

Here i am trying to achieve 2nd highest salary dept wise.

SELECT * FROM (SELECT SALARY,UPPER(DEPT) ,RANK()OVER(PARTITION BY DEPT ORDER BY SALARY) RNK FROM TEST_DATA)
WHERE RNK=2;

In case of RANK() we may get same rank for the same salary in the same department so in that case it will not give the correct result and It will give the next value also wrong(e.g. after repeating the value 2,2,2 next rank it will give 3) which will make all rank wrong and not useful for further use.
SELECT * FROM(
SELECT SALARY,UPPER(DEPT) ,DENSE_RANK()OVER(PARTITION BY DEPT ORDER BY SALARY) DRNK FROM TEST_DATA.

DENSE_RANK() will also do the same thing as rank just one thing it will not give the next value after repeating a value(e.g. after repeating 2,2,2 three times it will give next dense_rank value as 5) but again if we want to see the second highest salary it will show 3 values for dense_rank 2.
)WHERE DRNK=2;

SELECT * FROM(
SELECT SALARY,UPPER(DEPT) ,ROW_NUMBER()OVER(PARTITION BY DEPT ORDER BY SALARY) RNM FROM TEST_DATA;

Here it will give the exact value which we are looking for. So the best way to calculate highest values department wise use row_number(), it will always give the correct result.

please let me know for any concerns

  Was this answer useful?  Yes

SIDDHARTHA PENCHALA

  • Nov 27th, 2016
 

Can you please explain its internal process?

  Was this answer useful?  Yes

PRUDHVI

  • Dec 5th, 2016
 

Code
  1. SELECT DEPTNO, MAX(SAL) FROM EMP <br />

  2. WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP)<br />

  3. GROUP BY DEPTNO

  Was this answer useful?  Yes

KAMLESH

  • Dec 28th, 2016
 

Code
  1. SELECT DEPARTMENT_ID,SALARY FROM

  2. (SELECT DEPARTMENT_ID,SALARY,DENSE_RANK() OVER

  3. (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC)

  4. AS RNK FROM EMPLOYEES) WHERE RNK=2;

  Was this answer useful?  Yes

pinky

  • May 31st, 2017
 

SELECT * FROM (SELECT DEPT_ID , DENSE_RANK(SALARY) OVER(PARTITION BY DEPT_ID ORDER BY SALARY ) DRANK FROM DEPARTMENTS ) WHERE DRANK = 2;

  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.

 

Related Answered Questions

 

Related Open Questions