1 to many Select query with aggregate function (Adventure Works database)

I am practicing my SQL with the Adventure Works Database. My task is simple. I want to look up an employee and see how much money they make.

First Name| Last Name| Age| Pay Rate|

The problem is that the Pay Rate is located in a table with a 1 to many relationship with the employee (EmployeePayHistory) with the column ModifiedDate. I want to grab the most recent ModifiedDate but nothing I have tried works. I keep getting caught up on the aggregate function in my sub query

SELECT e.BusinessEntityID,p.FirstName [First Name], p.LastName [Last Name], DATEDIFF(YEAR,e.BirthDate, GETDATE() )[Age],
(SELECT eph1.Rate FROM HumanResources.EmployeePayHistory eph1 HAVING eph1.Rate = MAX(eph.ModifiedDate))
FROM Person.Person p 
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
GROUP BY e.BusinessEntityID, p.FirstName,p.LastName, DATEDIFF(YEAR,e.BirthDate, GETDATE() )

This is a greatest-n-per-group problem. Don't think aggregation: think filtering instead.

I guess this does what you want:

SELECT 
    e.BusinessEntityID
    p.FirstName [First Name], 
    p.LastName [Last Name], 
    DATEDIFF(YEAR,e.BirthDate, GETDATE()) [Age],
    (
        SELECT TOP (1) eph.Rate 
        FROM HumanResources.EmployeePayHistory eph 
        WHERE eph.BusinessEntityID = p.BusinessEntityID
        ORDER BY eph.ModifiedDate DESC
    ) [LatestRate]
FROM Person.Person p 
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID

Note: you did not tell exactly what the relation is between Person and Employee - the above assumes that this is a 1-1 relationship.