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.

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

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.

Jessica

Feb 5th, 2014

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

kumar

May 29th, 2014

Use join to join with dept table for desired result

Code

SELECT MAX(A.SAL),A.DNO FROM EMP A,(SELECT MAX(SAL) MSA,DNO FROM EMP GROUPBY DNO) B

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

GROUPBY A.DNO;

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)

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

Sambit Aadi

Nov 22nd, 2014

See and apply the correct answer

Code

SELECT deptno,MAX(sal)"SAL"

FROM emp

WHERE(deptno,sal)NOTIN(SELECT deptno,MAX(sal)

FROM emp

GROUPBY deptno)

GROUPBY deptno

/

Sivaji Bonu

Nov 28th, 2014

Code

SELECT*

FROM(

SELECT emp.*,

dense_rank() over ( partition BY deptno ORDERBY sal DESC) NRANK

FROM emp

)

WHERE NRANK = &n;

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

mohammed khan

Dec 5th, 2014

Code

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

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

Abhishek

Feb 2nd, 2015

Code

SELECT*

FROM scott.emp e1

WHERE1=(SELECT Count(DISTINCT Sal)

FROM scott.emp e2

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

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

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

kamlesh

Sep 26th, 2015

Code

SELECT DEPTNO,DNAME,SECOND_HIGH_SALARY FROM

(SELECT D.DEPTNO,D.DNAME,SALARY,DENSE_RANK() OVER (PARTITIONBY D.DEPTNO,D.DNAME ORDERBY E.SALARY)ASRANKFROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO)

WHERERANK=2;

Bighnaraj Dalai

Oct 6th, 2015

Code

SELECTMAX(sal)FROM emp WHERE sal NOTIN(SELECTMAX(sal)FROM emp) ;

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

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.

abhiecstaticProfile Answers by abhiecstatic Questions by abhiecstatic

Questions by abhiecstatic

## Related Answered Questions

## Related Open Questions