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 - 29 of 29 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.  

  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

Deepu

  • May 16th, 2013
 

Pls. use the below

Code
  1.  

  Was this answer useful?  Yes

Girija

  • Jun 8th, 2013
 

Code
  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.  

  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

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. span style="color: #ff0000;">"SAL"

  Was this answer useful?  Yes

Sivaji Bonu

  • Nov 28th, 2014
 

Code
  1.  


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

  Was this answer useful?  Yes

mohammed khan

  • Dec 5th, 2014
 

Code
  1.  

  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.  





  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.  

  Was this answer useful?  Yes

Bighnaraj Dalai

  • Oct 6th, 2015
 

Code
  1.  

  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.  

  Was this answer useful?  Yes

Ashok

  • Mar 21st, 2016
 

Code
  1.  

  Was this answer useful?  Yes

Raju

  • Apr 26th, 2016
 

Code
  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

bibek

  • Jul 2nd, 2021
 

this is nice and simple way to solve.. thank you

  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