# How to find out department wise second maximum salary.

#### Irfan

• Mar 30th, 2006

```CodeSELECT deptno,
sal,
dr
FROM
(SELECT deptno,
sal,
dense_rank()over(partition BY deptno
ORDER BY sal DESC) dr
FROM emp)
WHERE dr = 2

```

#### sreelekha Profile Answers by sreelekha

• Apr 9th, 2006

```CodeSELECT max(salary)
FROM dept
WHERE max(salary)<
(SELECT MAX (salary)
FROM dept);
```

#### ratna_ponnapalli Profile Answers by ratna_ponnapalli Questions by ratna_ponnapalli

• Apr 11th, 2006

```CodeSELECT max(sal)
FROM emp
WHERE sal NOT IN
(SELECT max(sal)
FROM emp
GROUP BY deptno)
GROUP BY deptno;
```

#### ardsouza Profile Answers by ardsouza

• May 16th, 2006

```CodeSELECT DISTINCT d1.deptno,
e1.sal
FROM emp e1,
dept d1
WHERE 2 =
(SELECT count(DISTINCT e2.sal)
FROM emp e2,
dept d2
WHERE e2.sal >= e1.sal
AND d2.deptno = e2.deptno
AND d1.deptno = d2.deptno)
AND d1.deptno = e1.deptno```

#### Bhai

• Jun 9th, 2006

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

• Apr 22nd, 2008

```CodeSELECT max(sal)
FROM
WHERE sal NOT IN
(SELECT max(sal)
FROM
GROUP BY deptno)
GROUP BY deptno;
```

Try this it is working

#### Imran_Javed Profile Answers by Imran_Javed

• May 9th, 2008

```CodeSELECT *
FROM
(SELECT ename ,
sal,
deptno ,
rank() over (PARTITION BY deptno
ORDER BY sal DESC) max_sal
FROM emp)
WHERE max_sal= 2;

```

#### monika1985 Profile Answers by monika1985

• May 28th, 2008

```CodeSELECT max(sal)
FROM empwhere sal NOT IN
(SELECT max(sal)
FROM emp
GROUP BY deptno)
GROUP BY deptno

```

#### mamtachaplot Profile Answers by mamtachaplot

• Aug 6th, 2008

```CodeSELECT salary,
deptid
FROM emp e1
WHERE 2=
(SELECT count(DISTINCT(salary))
FROM emp e2
WHERE e1.salary<=e2.salary
GROUP BY deptid)

```

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

#### deepikazain Profile Answers by deepikazain Questions by deepikazain

• Feb 27th, 2010

```CodeSELECT *
FROM
(SELECT t.*,
rownum rnum
FROM
(SELECT sal
FROM employee
ORDER BY comp_dept DESC) t
WHERE rownum<2)
WHERE rnum>2

```

Deepika Jain

#### agssiva Profile Answers by agssiva

• Mar 25th, 2010

```CodeSELECT*
FROM Emptbl e
INNER JOIN
(SELECT Dep,
MAX(salary) AS sal
FROM Emptbl
GROUP BY Dep) AS t ON t.Dep = e.Dep
WHERE e.Salary < t.sal

```

#### rashmi.purbey Profile Answers by rashmi.purbey

• Jul 7th, 2010

Table Name: employee
columns: employee,salary, dept

```CodeSELECT MAX (e1.salary) , e1.dept
FROM employee e1
WHERE e1.salary<
(SELECT MAX (e2.salary)
FROM employee e2
WHERE e1.dept= e2.dept
GROUP BY e2.dept)
GROUP BY e1.dep

```

• Jun 6th, 2011

```CodeSELECT *
FROM emp e,
(SELECT dept,
MAX(salary) AS salary
FROM emp
GROUP BY dept) e1
WHERE e1.dept=e.dept
AND e.salary=e1.salary```

#### sagar

• Sep 29th, 2011

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

#### Mohan Rao

• Nov 3rd, 2011

```CodeSELECT max(sal) ,
deptno
FROM emp e1 ,
(SELECT max(sal) e2sal ,
deptno e2dept
FROM emp
GROUP BY deptno) e2
WHERE e2.e2dept = e1.dept
AND e1.sal < e2.e2sal
GROUP BY e1.deptno

```

#### sharika

• Nov 24th, 2011

```CodeSELECT LEVEL,max(salary)
FROM employee
WHERE LEVEL=&LEVEL NO CONNECT BY
PRIOR salary>salary
GROUP BY LEVEL;

```

#### shruthyreddy Profile Answers by shruthyreddy

• Jan 9th, 2012

```CodeSELECT max(salary)
FROM emp
GROUP BY dept HAVING salary <
(SELECT max(salary)
FROM emp
GROUP BY dept)

```

#### srikanth

• Jan 11th, 2012

```CodeSELECT deptno,
max(salary)
FROM emp
WHERE salary NOT IN
(SELECT max(salary)
FROM emp
GROUP BY deptno)
GROUP BY deptno;

```

#### SASI

• Apr 7th, 2012

```CodeSELECT EMPNO,
ENAME,
DEPTNO,
SAL
FROM EMP E1
WHERE 1 =
(SELECT COUNT(DISTINCT(SAL))
FROM EMP E2
WHERE E1.SAL <= E2.SAL)

```

#### vipinganganiya Profile Answers by vipinganganiya

• Apr 26th, 2012

select max(salary) from dept order by dept_id desc limit 1,1

#### Ashok

• May 4th, 2012

What is the length of column in oracle?

#### smily08 Profile Answers by smily08

• Aug 30th, 2012

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

#### smily08 Profile Answers by smily08

• Aug 30th, 2012

select max(salary) from dept order by dept_id desc limit 1,1

#### Nazeera Jaffar

• Sep 25th, 2012

```CodeSELECT min(salary)
FROM dept
WHERE salary IN ( SELECT DISTINCT top 2 salary
FROM dept
ORDER BY salary DESC)```

#### Mrinal

• Dec 14th, 2012

```CodeSELECT DISTINCT d1.depno,
e1.salary,e1.empid,d1.location
FROM EMPLOYEE1 e1,
dept d1
WHERE 2 =
(SELECT count(DISTINCT e2.salary)
FROM EMPLOYEE1 e2,
dept d2
WHERE e2.salary >= e1.salary
AND d2.depno = e2.Dep_No
AND d1.depno = d2.depno)
AND d1.depno = e1.Dep_No```

#### gd_naidu Profile Answers by gd_naidu

• Nov 8th, 2014

Tested code

```CodeSELECT * FROM  (SELECT emp.*, DENSE_RANK()
over (PARTITION BY deptno
ORDER BY sal DESC) scmax_sal FROM emp) WHERE scmax_sal IN 2;```

#### 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) = ∈

#### 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

#### Bismaya

• Sep 2nd, 2015

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

#### Siddhartha Rao Profile Answers by Siddhartha Rao

• Aug 11th, 2016

Can you explain the query internal process? like how salary > salary works..

#### 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

#### SIDDHARTHA PENCHALA

• Nov 27th, 2016

Can you please explain its internal process?

#### PRUDHVI

• Dec 5th, 2016

```CodeSELECT DEPTNO, MAX(SAL) FROM EMP <br />
WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP)<br />
GROUP BY DEPTNO ```

#### KAMLESH

• Dec 28th, 2016

```CodeSELECT DEPARTMENT_ID,SALARY FROM
(SELECT DEPARTMENT_ID,SALARY,DENSE_RANK() OVER
(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC)
AS RNK FROM EMPLOYEES) WHERE RNK=2;```

#### 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;

Send   Reset