Trying to pull most recent pay rate from table

I'm just 开发者_StackOverflow中文版stuck on something that seems so simple, but I can't figure out the syntax. I've a got a table of pay rates, associated with dates the rates changed, and employee ids. multiple records per employee.

I'm trying to get a list of the most recent date, and the wage from only that date. I've tried: SELECT MAX(Date), Rate, EmpID FROM History

but that doesn't work. It seems to return a record for each pay rate.

How would others do this efficiently?

---

This will give you the latest Rate for each EmpID.

select [Date],
       Rate,
       EmpID
from (select [Date],
             Rate,
             EmpID,
             row_number() over(partition by EmpID order by [Date] desc) as rn
      from History) as H
where rn = 1

Try here: https://data.stackexchange.com/stackoverflow/q/109020/

---

SELECT Date, Rate, EmpID
FROM   History A
WHERE  Date = (
           SELECT Max(Date)
           FROM   History B
           WHERE  A.EmpID = B.EmpId )

---

select * 
from payrates 
where date = (select max(dates) 
                from payrates);