Multi-table select, limiting number of results

So let’s say you had a query to retrieve stats on all baseball players in a db that has constantly updating stats such as walks, home runs, etc, such as:

SELECT pl.player_name, pl.player_number, bat.at_bats, pit.era 
FROM players pl
LEFT JOIN batting bat ON pl.player_id = bat.player_id
LEFT JOIN pitching pit ON pl.player_id = pit.player_id

Now say you also wanted to pull stats from another table, say a salaries table, but you wanted to only pull the most recent salary, so something like:

SELECT pl.player_name, pl.player_number, bat.at_bats, pit.era, sal.salary
FROM players pl
LEFT JOIN batting bat ON pl.player_id = bat.player_id
LEFT JOIN pitching pit ON pl.player_id = pit.player_id
LEFT JOIN salaries sal ON pl.player_id = sal.player_id

But you only want to get their most recent salary number for each player. How would you combine all of these? Hopefully I explained that clearly.

Answer

SELECT pl.player_name, pl.player_number, bat.at_bats, pit.era, s.salary
FROM players pl
LEFT JOIN batting bat ON pl.player_id = bat.player_id
LEFT JOIN pitching pit ON pl.player_id = pit.player_id
cross apply (select top 1 * from salaries sal where pl.player_id = sal.player_id order by 
              sal.SalaryDate desc) s