为每个员工选择最大记录

Question

我在MS Access里有一个员工资格表。我想为每个员工选择最新/最高薪酬记录：

ID(Autonumber)   
EmployeeID
EmployeeRate

So for example for employee nr 3 there would be
ID(Autonumber)   EmployeeID    EmployeeRate
1                3             100.00
2                3             150.00
3                10            110.00
4                10            160.00

如何选择记录2和4？

Answer 1

SELECT  a.EmployeeID, MAX(a.EmployeeRate)
FROM YourTable AS a
GROUP BY a.EmployeeID;

Answer 2

好吧，最新的vs最高利率是两个不同的概念。这应该会为您获得该员工的最新薪酬。

如果你想要最新版本，你可以这样做

select EmployeeID, EmployeeRate from YourTable a
join (select EmployeeID as EmployeeID , max(id) as id from YourTable group by EmployeeID) b on b.id=a.id