SQL Server second highest salary in each department

sbtkgmzw 于 12个月前发布在其他

关注(0)|答案(7)|浏览(114)

I am trying to find the second highest salary in each department.

Schema:

CREATE TABLE employees
( 
    ID int NOT NULL,
    NAME char(50) NOT NULL,
    departmentid int,
    salary int 
);

Sample records:

/*departmentid =1 */
INSERT INTO employees VALUES (1, 'Max', 1, 90000);
INSERT INTO employees VALUES (2, 'Joe', 1, 70000);
INSERT INTO employees VALUES (3, 'Randy', 1, 70000);

/*departmentid =2 */
INSERT INTO employees VALUES (4, 'Henry', 2, 80000);
INSERT INTO employees VALUES (5, 'SAM', 2, 60000);

/*departmentid =3 */
INSERT INTO employees VALUES (6, 'Janet', 3, 69000);

My query:

SELECT departmentid, 
    NAME, 
    salary 
FROM   
    (
        SELECT 
            departmentid, 
            NAME, 
            salary, 
            Dense_rank()OVER (partition BY departmentid 
                          ORDER BY salary DESC) AS Rank, 
             Count(1)OVER(partition BY departmentid) AS cnt 
        FROM   
            employees
    )t 
WHERE  
    t.rank = 2 
    OR ( t.rank = 1 
         AND cnt = 1 )

The output I am getting is as below;

departmentid   NAME    salary

1              Joe      70000

1              Randy    70000

2              SAM      60000

3              Janet    69000

My expected output is

departmentid   NAME    salary

1              Joe      70000

1              Randy    70000

2              SAM      60000

3              NULL     NULL

As there is only one record for departmentid=3, it should return null.

What is wrong with this query? Any other ways to achieve this result?

I've also included a SQL fiddle.

sql-server

来源：https://stackoverflow.com/questions/46905413/second-highest-salary-in-each-department

7条答案

按热度按时间

3gtaxfhh1#

ROW_NUMBER() and select = 2

;WITH salary AS
    (
     [RN] = SELECT ROW_NUMBER() OVER (PARTITION BY departmentid ORDER BY salary),*
    FROM <table>
    )
    SELECT 
    *
    FROM salary
    WHERE [RN] = 2

赞(0）回复(0）举报 12个月前

dffbzjpn2#

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)

赞(0）回复(0）举报 12个月前

6ojccjat3#

Use a sub query as i wrote here : http://sqlfiddle.com/#!6/bb5e1/26

with ranks as(
 SELECT departmentid, 
       salary,
       row_number() over (partition by (departmentid) order by salary desc) as rank
FROM   employees
   )
   Select * 
   from ranks
Where ranks.rank = 2

赞(0）回复(0）举报 12个月前

13z8s7eq4#

If the departmentid having only one row, and if you consider that also. Then

Query

;with cte as(
  select [rank] = dense_rank() over(
    partition by departmentid
    order by departmentid, salary desc
  ), *
  from employees
)
select ID, NAME, departmentid, salary from cte
where [rank] = 2
union all
select max(ID), max(NAME), departmentid, max(salary)
from cte
group by departmentid
having count([rank]) = 1;

赞(0）回复(0）举报 12个月前

y1aodyip5#

There is also a simple way:

SELECT TOP 1 * FROM (Select top 2 * FROM employees order by salary desc ) e Order by salary asc

Edit: this returns only the 2nd highest overall

赞(0）回复(0）举报 12个月前

f45qwnt86#

I think you can get correct answer by just removing below code from your code

OR ( t.rank = 1 
         AND cnt = 1 )

also main table should be left join from this result to get null in rest of columns

赞(0）回复(0）举报 12个月前

y0u0uwnf7#

--First approach
select e.dept, max(e.salary) salary
FROM 
EMPLOYEE e
outer apply  (
    select dept, MAX(salary) salary
    FROM EMPLOYEE a
    where e.dept = a.dept
    GROUP BY DEPT
) o 
where e.salary < o.salary
GROUP by e.dept
GO

--Second Approach
SELECT DEPT, MAX(SALARY)
FROM (
    SELECT DEPT, SALARY
    FROM EMPLOYEE
    EXCEPT
    SELECT DEPT, MAX(SALARY) SALARY
    FROM EMPLOYEE
    GROUP BY DEPT
) A
GROUP BY A.DEPT
GO

赞(0）回复(0）举报 12个月前

我来回答

SQL Server second highest salary in each department

7条答案

相关问题

热门标签

最新问答