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
SELECT


  tab.department_name,


  MIN(tab.salary) AS Second_Max_Sal


FROM


  (


    SELECT


      e.first_name,


      e.salary,


      d.department_name,


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


      rank


    FROM


      hr.departments d


    JOIN hr.employees e USING (department_id)


  )


  tab


WHERE


  rank BETWEEN 1 AND 2


GROUP BY


  tab.department_name

Vijaya

• May 16th, 2013

 

Use Rank in sub query.

Look at more Rank and Dense_rank functions

Deepu

• May 16th, 2013

 

Pls. use the below

Code
SELECT


T1.DEPTNO, T1.DNAME,MIN(T1.SAL) AS SECOND_MAX_SAL FROM


(SELECT E.FIRST_NAME, E.SAL, D.DEPTNO,D.DNAME, DENSE_RANK() OVER (PARTITION BY D.DEPTNO, D.DNAME ORDER BY E.SAL) 


AS RANK FROM DEPT D,EMP E 


WHERE E.DEPTNO=D.DEPTNO) T1


WHERE RANK BETWEEN 1 AND 2 GROUP BY T1.DNAME, T1.DEPTNO;

Rakesh Protihar

• May 17th, 2013

 

Code
SELECT e.DeptNo, MAX(e.Sal),d.DeptName Salary


FROM Emp e left outer join dept d ON e.DeptNo=d.DeptNo


WHERE e.Sal <


(SELECT MAX(Sal)


 FROM Emp


 WHERE DeptNo = e.DeptNo)


GROUP BY e.DeptNo,d.DeptName

Girija

• Jun 8th, 2013

 

Code
SELECT *


  FROM (SELECT employee_id, last_name, department_id, salary,


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


FROM employees) a


WHERE a.rn = 1

srini

 Profile Answers by 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

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 GROUP BY DNO) B


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


GROUP BY 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)

Jatin

 Profile Answers by Jatin 

• Oct 31st, 2014

 

Easy as follow :)

Code
SELECT MAX(SAL), DEPTNO


FROM EMP


WHERE SAL NOT IN 


               (


               SELECT MAX(SAL)


               FROM EMP


               )


               GROUP BY DEPTNO;

Ravi

• Nov 13th, 2014

 

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) NOT IN (SELECT deptno,MAX(sal) 


                           FROM emp 


                           GROUP BY deptno)


GROUP BY deptno


/


 

Sivaji Bonu

• Nov 28th, 2014

 

Code
SELECT *


    FROM (


         SELECT emp.*,


         dense_rank( ) over ( partition BY deptno ORDER BY 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 ORDER BY 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


 WHERE 1 = (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 (PARTITION BY D.DEPTNO,D.DNAME ORDER BY E.SALARY) AS RANK FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO) 


WHERE RANK=2;

Bighnaraj Dalai

• Oct 6th, 2015

 

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

kirti

• Nov 16th, 2015

 

Why it needs () symbol after rank?

Anubhay Jha

• Feb 4th, 2016

 

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


FROM DEPT D,EMP E WHERE 1=1


AND e.deptno = d.deptno


GROUP BY D.DEPTNO,D.Dname;

sumanamara2016

 Profile Answers by sumanamara2016 

• Mar 18th, 2016

 

Code
SELECT SAL FROM EMP A


 WHERE &N=


  (SELECT COUNT(DISTINCT B.SAL) 


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


 


SELECT * FROM (


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


) WHERE RNK=2;


 

Ashok

• Mar 21st, 2016

 

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


FROM dept d,emp e 


WHERE 


   e.deptno=d.deptno 


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


GROUP BY d.deptno,d.dname

Raju

• Apr 26th, 2016

 

Code
SELECT *


  FROM (SELECT e.*,


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


          FROM emp e)


 WHERE a = 1;

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
/

kishanjha

 Profile Answers by kishanjha 

• May 20th, 2017

 

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


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


GROUP BY e.DEPARTMENT_ID,d.department_name

bibek

• Jul 2nd, 2021

 

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