SQL select highest column value with select from multiple tables

Question

I'm trying to view only the highest value in the RATE column while combining the FirstName, LastName, VacationHours, SickLeaveHours, and HireDate from the other two tables.

Basically, I want to only see the current RATE of each employee without seeing their previous pay history.

SELECT HumanResources.EmployeePayHistory.EmployeeID
,HumanResources.EmployeePayHistory.RateChangeDate
,HumanResources.EmployeePayHistory.Rate
,HumanResources.EmployeePayHistory.PayFrequency
,HumanResources.EmployeePayHistory.ModifiedDate
,HumanResources.vEmployee.FirstName
,HumanResources.vEmployee.LastName
,HumanResources.Employee.VacationHours
,HumanResources.Employee.SickLeaveHours
,HumanResources.Employee.HireDate
FROM [AdventureWorks].[HumanResources].[EmployeePayHistory],
[AdventureWorks].[HumanResources].[vEmployee], 
[AdventureWorks].[HumanResources].[Employee]
WHERE HumanResources.EmployeePayHistory.EmployeeID = HumanResources.vEmployee.EmployeeID 
AND HumanResources.EmployeePayHistory.EmployeeID = HumanResources.Employee.EmployeeID[enter image description here][1]

Answer 1

You can use the row_number() window function to number each employee's rate change and then just take the last one.

Regardless, it's worth mentioning that using implicit joins (i.e., multiple tables in the from clause) is considered as a deprecated syntax, and you should probably use explicit join clauses instead:

SELECT EmployeeID,
       RateChangeDate,
       Rate,
       PayFrequency,
       ModifiedDate,
       FirstName,
       LastName,
       VacationHours,
       SickLeaveHours,
       HireDate
FROM   (SELECT eph.EmployeeID,
               eph.RateChangeDate,
               eph.Rate,
               eph.PayFrequency,
               eph.ModifiedDate,
               ve.FirstName,
               ve.LastName,
               e.VacationHours,
               e.SickLeaveHours,
               e.HireDate,
               ROW_NUMBER() OVER (PARTITION BY eph.EmployeeID
                                  ORDER BY     eph.RateChangeDate DESC) AS rn
        FROM   AdventureWorks].[HumanResources].[EmployeePayHistory] eph
        JOIN   [AdventureWorks].[HumanResources].[vEmployee] ve ON 
               eph.EmployeeID = ve.EmployeeID
        JOIN   [AdventureWorks].[HumanResources].[Employee] e ON 
               eph.EmployeeID = e.EmployeeID) t
WHERE    rn = 1