如何获得薪酬最高的员工行

A table consists of employee name, address, phone, department, salary:

How to get the highest paid employee row from each department?

I tried with

select dept, max(salary) from employee group by dept

but it gives only two columns. But I want to select an entire row. How to do it?

Alternatively how to add more columns to the result?

(I am using SQL Server 2008)

#0

You simply need to join the query you currently have back to the employee table to get the full employee information.

select e.*
from employee e
inner join (select dept, max(salary) ms from employee group by dept) m
  on e.dept = m.dept and e.salary = m.ms

#1

SELECT name,
       address,
       phone,
       department,
       salary,
       dept
FROM   (SELECT name,
               address,
               phone,
               department,
               salary,
               dept,
               row_number() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
        FROM   employee) AS e
WHERE  e.rn = 1  

Using row_number() will give you one row if there is a tie for the highest salary. If you want all the highest salaries for each department you should use rank() instead.

SELECT name,
       address,
       phone,
       department,
       salary,
       dept
FROM   (SELECT name,
               address,
               phone,
               department,
               salary,
               dept,
               rank() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
        FROM   employee) AS e
WHERE  e.rn = 1

#2

with cte as (
  select *, rank() over (partition by dept order by salary desc) as [r]
  from employees
)
select * from cte where [r] = 1;

#3

Something like this?

select * from employee where salary = (select max(salary) from employee)

#4

select * from employee 
where salary in
(select  max(salary) from employee group by dept);

推荐文章

如何实现dislike按钮

如何实现dislike按钮

推荐文章

接收麦克风输入和处理

接收麦克风输入和处理

推荐文章

如何释放具有相同委托的多个类?

如何释放具有相同委托的多个类?

推荐文章

使用SqlAlchemy和SqlAlchemy migrate管理种子数据

使用SqlAlchemy和SqlAlchemy migrate管理种子数据

推荐文章

Android音乐播放器小部件

Android音乐播放器小部件

推荐文章

Tomcat/Hudson无法连接到Github

Tomcat/Hudson无法连接到Github

推荐文章

Android:如何创建只执行一次的函数?

Android:如何创建只执行一次的函数?

推荐文章

SL4->MEF DeploymentCatalog无法下载具有SecurityException的文件

SL4->MEF DeploymentCatalog无法下载具有SecurityException的文件

推荐文章

Visual Studio:在制表符和空格缩进之间是否有一键式切换?

Visual Studio:在制表符和空格缩进之间是否有一键式切换?

推荐文章

Vim关闭缓冲区但不拆分窗口

Vim关闭缓冲区但不拆分窗口

推荐文章

AppDomain中的静态字段

AppDomain中的静态字段

推荐文章

Jquery加载onclick数据驱动定制系统

Jquery加载onclick数据驱动定制系统

推荐文章

具有不同高度的分组UITableView行导致内容乱序

具有不同高度的分组UITableView行导致内容乱序

推荐文章

第二版代码补全

第二版代码补全

推荐文章

在C中传递泛型委托作为方法参数#

在C中传递泛型委托作为方法参数#

推荐文章

回滚app_controller.php,cakephp仍在筛选

回滚app_controller.php,cakephp仍在筛选