How to Get Second Highest Salary Department Wise Without Using Analytical Functions

How to get second highest salary department wise?

I think there are several way to solve the problem. Following solution from my side and works fine.

SELECT *
From employee e2 
WHERE e2.salary = (SELECT distinct salary FROM employee where dept_id=e2.dept_id order by salary desc limit 1,1);

I need only second highest salary value with department wise which is the input array of next operation in my project. Finally I use

SELECT e2.dept_id, max(e2.salary)
From employee e2
WHERE e2.salary = (SELECT distinct salary FROM employee where dept_id=e2.dept_id order by salary desc limit 1,1)
group by e2.dept_id

second highest salary in each department

I've used two CTEs.

The first returns a list of every department. You'll need this to ensure departments with less than 2 salaries are included in the final result.

The second ranks each employee within their department.

Finally, I've used a left outer join to maintain the complete list of departments.

WITH Department AS
(
  -- Returns a list of the departments.
  SELECT 
    departmentid
  FROM
    employees
  GROUP BY
    departmentid
),
EmployeeRanked AS
(
  SELECT
    DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS [Rank],
    departmentid,
    NAME,
    salary
  FROM
    employees
)
SELECT 
  er.Rank,
  d.departmentid,
  er.NAME,
  er.salary
FROM 
  Department AS d
    LEFT OUTER JOIN EmployeeRanked AS er    ON er.departmentid = d.departmentid
                                            AND er.[Rank] = 2
;

Returns

Rank    departmentid    NAME    salary
2       1               Joe     70000
2       1               Randy   70000
2       2               SAM     60000
(null)  3               (null)  (null)

I need to fetch the second highest salary per department using correlated subquery and oracle sql

I don't have your tables so I'll use Scott's EMP. This is its contents:

SQL> select deptno, ename, sal from emp order by deptno, sal desc;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450   --> 2nd highest in deptno 10
        10 MILLER           1300
        20 SCOTT            3000
        20 FORD             3000
        20 JONES            2975   --> 2nd highest in deptno 20
        20 ADAMS            1100
        20 SMITH             800
        30 BLAKE            2850
        30 ALLEN            1600   --> 2nd highest in deptno 30
        30 TURNER           1500
        30 MARTIN           1250
        30 WARD             1250
        30 JAMES             950

14 rows selected.

This is what you don't want:

SQL> with temp as
  2    (select deptno, ename, sal,
  3       dense_rank() over (partition by deptno order by sal desc) rnk
  4     from emp
  5    )
  6  select *
  7  from temp
  8  where rnk = 2
  9  order by deptno, sal desc;

    DEPTNO ENAME             SAL        RNK
---------- ---------- ---------- ----------
        10 CLARK            2450          2
        20 JONES            2975          2
        30 ALLEN            1600          2

SQL>

---

OK, let's correlate some subqueries, then. Return employees whose salary is

• lower than the highest in their department (line #6) (it would rank as the 1st)
• the highest for the rest of salaries in their department (line #3)

So:

SQL> select e.deptno, e.ename, e.sal
  2  from emp e
  3  where e.sal = (select max(b.sal)
  4                 from emp b
  5                 where b.deptno = e.deptno
  6                   and b.sal < (select max(a.sal)
  7                                from emp a
  8                                where a.deptno = b.deptno
  9                                group by a.deptno
 10                               )
 11                 )
 12  order by e.deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        20 JONES            2975
        30 ALLEN            1600

SQL>

HiveQL to find the second largest salary from the employee table?

In case of ties the accepted answer won't work. So below is my code which works in all situations. Just replaced row_number with dense_rank that's it. Want to know more about dense_rank then visit this link

select * from  (SELECT dep_name,salary,DENSE_RANK() over(ORDER BY salary desc) as rank FROM department) as A where rank = 2;

OUTPUT:

+--------+------+----------+
|dep_name|salary|     rank |
+--------+------+----------+
|      CS| 30000|         2|
|   CIVIL| 30000|         2|
+--------+------+----------+  

Hope it helps!

Find the 3rd Maximum Salary for each department based on table data

Select EmpID,empname,deptid,salary
 From (
Select *
      ,RN  = Row_Number() over (Partition By deptid Order By Salary)
      ,Cnt = sum(1) over (Partition By deptid)
 From  employee1
      ) A
 Where RN = case when Cnt<3 then Cnt else 3 end

Returns

I want to query difference between first and second highest salary

Use the DENSE_RANK (if you want highest two unique salary values) or ROW_NUMBER (if you want the salary of the two highest earning people regardless of ties) analytic functions and then use conditional aggregation:

SELECT MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
  WHERE  d.loc = 'New York'
);

Which, for the sample data:

CREATE TABLE emp ( deptno, sal ) AS
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 1000 FROM DUAL UNION ALL
SELECT 1,  500 FROM DUAL UNION ALL
SELECT 1,  200 FROM DUAL UNION ALL
SELECT 2, 2000 FROM DUAL UNION ALL
SELECT 2, 1500 FROM DUAL UNION ALL
SELECT 2,  200 FROM DUAL UNION ALL
SELECT 2,  700 FROM DUAL;

CREATE TABLE dept ( deptno, loc ) AS
SELECT 1, 'New York' FROM DUAL UNION ALL
SELECT 2, 'Beijing'  FROM DUAL;

Outputs:

SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-------------------------: | -----------------------:
                      1000 |                        0

---

Or, if you want all the departments:

SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;

Which outputs:

DEPTNO | SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-----: | -------------------------: | -----------------------:
     1 |                       1000 |                        0
     2 |                        500 |                      500

db<>fiddle here

---

Related Topics