How to retrieve 2nd highest sal in each departement from emp and dept tables using GROUP BY?

EMP table (empno,deptno,sal)
DEPT table(deptno,dname)
i need Deptno, Dname, 2nd_highest_sal_in_dept
in output.
I can easily do this using row_number, Rank,Dense_rank etc but I am unable to do this using Group By.
Please suggest if this can be done using Group By.

Questions by abhiecstatic

Showing Answers 1 - 28 of 28 Answers

Amod

  • Jan 3rd, 2013
 

Second max salary per department. If it does not exist use max salary (Case when there is one employee in the department)

Code
  1. SELECT

  2.   tab.department_name,

  3.   MIN(tab.salary) AS Second_Max_Sal

  4. FROM

  5.   (

  6.     SELECT

  7.       e.first_name,

  8.       e.salary,

  9.       d.department_name,

  10.       dense_rank() over (partition BY d.department_name ORDER BY e.salary) AS

  11.       rank

  12.     FROM

  13.       hr.departments d

  14.     JOIN hr.employees e USING (department_id)

  15.   )

  16.   tab

  17. WHERE

  18.   rank BETWEEN 1 AND 2

  19. GROUP BY

  20.   tab.department_name

  Was this answer useful?  Yes

Vijaya

  • May 16th, 2013
 

Use Rank in sub query.

Look at more Rank and Dense_rank functions

  Was this answer useful?  Yes

Girija

  • Jun 8th, 2013
 

Code
  1. SELECT *

  2.   FROM (SELECT employee_id, last_name, department_id, salary,

  3.         DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC)rn

  4. FROM employees) a

  5. WHERE a.rn = 1

  Was this answer useful?  Yes

srini

  • Dec 6th, 2013
 

We will get 2nd Max salary for each dept:

SELECT max(e1.sal), e1.deptno FROM s_emp e1
WHERE sal < (SELECT max(sal) FROM s_emp e2
WHERE e2.deptno = e1.deptno)
GROUP BY e1.deptno

  Was this answer useful?  Yes

vishwanath

  • Jan 23rd, 2014
 

Truncate:- 1)The number of deleted rows are not returned .2)It is auto commit
Delete:- 1) The number of deleted rows are returned .3) It is not auto commi.

  Was this answer useful?  Yes

Jessica

  • Feb 5th, 2014
 

Please read the question then answer it; post the answer after verify the results.

  Was this answer useful?  Yes

kumar

  • May 29th, 2014
 

Use join to join with dept table for desired result

Code
  1. SELECT MAX(A.SAL),A.DNO FROM EMP A,(SELECT MAX(SAL) MSA,DNO FROM EMP GROUP BY DNO) B

  2. WHERE A.SAL <> MSA AND A.DNO = B.DNO

  3. GROUP BY A.DNO;

  Was this answer useful?  Yes

Rs

  • Aug 6th, 2014
 

No need to use group by to get second highest salary.. use simple code as shown below...

select max(salary) from emp where salary < (select max(salary) from emp)

  Was this answer useful?  Yes

Jatin

  • Oct 31st, 2014
 

Easy as follow :)

Code
  1. SELECT MAX(SAL), DEPTNO

  2. FROM EMP

  3. WHERE SAL NOT IN

  4.                (

  5.                SELECT MAX(SAL)

  6.                FROM EMP

  7.                )

  8.                GROUP BY DEPTNO;

  Was this answer useful?  Yes

Ravi

  • Nov 13th, 2014
 

If there is only 1 salary in a table, then it does not get displayed.

  Was this answer useful?  Yes

Sambit Aadi

  • Nov 22nd, 2014
 

See and apply the correct answer

Code
  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

  7. /

  8.  

  Was this answer useful?  Yes

Sivaji Bonu

  • Nov 28th, 2014
 

Code
  1. SELECT *

  2.     FROM (

  3.          SELECT emp.*,

  4.          dense_rank( ) over ( partition BY deptno ORDER BY sal DESC) NRANK

  5.          FROM emp

  6.          )

  7.     WHERE NRANK = &n;


Here n is the position of salary which u want to display.....

  Was this answer useful?  Yes

mohammed khan

  • Dec 5th, 2014
 

Code
  1. SELECT * FROM (SELECT S.*,DENSE_RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) DR FROM SOURCE ) S WHERE S.DR=2;

  Was this answer useful?  Yes

Dasish

  • Dec 12th, 2014
 

Select a.empno, a.sal from emp a, emp b where a.sal <= b.sal group by a.empno, a.sal having count(distinct b.sal) = 2
This is the best example, which will u give the better understanding of non equi join and aggregate functions

  Was this answer useful?  Yes

Abhishek

  • Feb 2nd, 2015
 

Code
  1. SELECT *

  2.   FROM scott.emp e1

  3.  WHERE 1 = (SELECT Count(DISTINCT Sal)

  4.          FROM scott.emp e2

  5.         WHERE e1.deptno = e2.deptno AND e1.sal < e2.sal )





  Was this answer useful?  Yes

Ejaz Ahmed

  • Apr 19th, 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

BIKAS

  • Sep 22nd, 2015
 

I dont think this will give the desired result. This might return either first highest or second highest randomly since first max is present for all

  Was this answer useful?  Yes

kamlesh

  • Sep 26th, 2015
 

Code
  1. SELECT DEPTNO,DNAME,SECOND_HIGH_SALARY FROM

  2.  (SELECT D.DEPTNO,D.DNAME,SALARY,DENSE_RANK() OVER (PARTITION BY D.DEPTNO,D.DNAME ORDER BY E.SALARY) AS RANK FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO)

  3. WHERE RANK=2;

  Was this answer useful?  Yes

Bighnaraj Dalai

  • Oct 6th, 2015
 

Code
  1. SELECT MAX(sal) FROM emp WHERE sal NOT IN (SELECT MAX(sal) FROM emp) ;

  Was this answer useful?  Yes

kirti

  • Nov 16th, 2015
 

Why it needs () symbol after rank?

  Was this answer useful?  Yes

Anubhay Jha

  • Feb 4th, 2016
 

Code
  1. SELECT D.deptno,MAX(E.SAL),d.dname

  2. FROM DEPT D,EMP E WHERE 1=1

  3. AND e.deptno = d.deptno

  4. GROUP BY D.DEPTNO,D.Dname;

  Was this answer useful?  Yes

Code
  1. SELECT SAL FROM EMP A

  2.  WHERE &N=

  3.   (SELECT COUNT(DISTINCT B.SAL)

  4.     FROM EMP B WHERE A.SAL<=B.SAL);

  5.  

  6. SELECT * FROM (

  7. SELECT EMPNO,ENAME,SAL, RANK() OVER(ORDER BY SAL DESC) RNK FROM EMP

  8. ) WHERE RNK=2;

  9.  

  Was this answer useful?  Yes

Ashok

  • Mar 21st, 2016
 

Code
  1. SELECT d.deptno,d.dname,MAX(e.sal)

  2. FROM dept d,emp e

  3. WHERE

  4.    e.deptno=d.deptno

  5.    AND sal<(SELECT MAX(e1.sal) FROM emp e1 WHERE e1.deptno=d.deptno)

  6. GROUP BY d.deptno,d.dname

  Was this answer useful?  Yes

Raju

  • Apr 26th, 2016
 

Code
  1. SELECT *

  2.   FROM (SELECT e.*,

  3.                DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) a

  4.           FROM emp e)

  5.  WHERE a = 1;

  Was this answer useful?  Yes

prakash

  • Apr 18th, 2017
 

SELECT * FROM(SELECT DEPTNO,ENAME,SAL ,DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)R FROM EMP) WHERE R=2
/

  Was this answer useful?  Yes

kishanjha

  • May 20th, 2017
 

Code
  1. SELECT e.DEPARTMENT_ID,Max(e.SALARY) AS Sal,d.department_name FROM Employees e,Departments d

  2. WHERE e.SALARY NOT IN (SELECT Max(SALARY) FROM Employees GROUP BY DEPARTMENT_ID)AND e.DEPARTMENT_ID=d.DEPARTMENT_ID

  3. GROUP BY e.DEPARTMENT_ID,d.department_name

  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