Help with the MAX() function

In a recent forum posting, aoneistheman wrote:

"I'm a SQL newbie, and am creating this crazy view of other views. I need help using the MAX() function.

I have a table that holds a wage id, a person id, salary, and salary start date. A person that gets a raise, gets a new record with a different salary, and a new wage id. I need the record with the latest date. So I do a simple select:

SELECT person_id, MAX(sal_str_dt) as Salary_Strt_Date
FROM Salaries
GROUP BY person_id

Results:

id Salary_Strt_Date
15 12312008
144 07011996

I also need the salary amount along with those two columns. Yet, SQL will not allow me to just add salary in the select statement, unless I give it the following MAX(Salary). I don't want the max salary though, as it may not be in the same record as the max date, and would mess up my results. Any Idea as to how I can get the salary that corresponds with the latest date, and still group by the person_id?"

Can you help? Interested in solutions to the same problem? Visit our forum and join the discussion!