LEFT OUTER JOIN (SELECT * FROM TABLE) is this possible?

Question

I have two tables Person and Salary.

Person:

PersonId   |  Name   |   Surname
--------------------------------
       1      John          Deer
       2      Mark          Bear

Salary:

SId | PersonId | Date                       | Salary
----------------------------------------------------
1            2   2013-01-01 00:00:00.000      100
2            2   2012-01-01 00:00:00.000      90
3            2   2011-01-01 00:00:00.000      80

What I am trying to do is, if a person has a salary record then it should display the most current salary info in the results, if no salary record then it should display the salary info as null, which is like...

Result
------------------------------------------------------------------------
PersonId   |   Name   | Surname  |  Date                      |  Salary
       1       John        Deer     NULL                            NULL
       2       Mark        Bear     2013-01-01 00:00:00.000         100

I know it has to be something like this but with lack of knowledge I just couldn't achieve..

SELECT 
    P.PersonId, P.Name, P.Surname, SL.Date, SL.Salary
FROM 
    PERSON P
LEFT OUTER JOIN 
    (SELECT TOP 1 S.PersonId, S.Date, S.Salary 
     FROM Salary 
     WHERE S.PersonId = P.PersonId ORDER BY Date DESC) SL

Answer 1

I would start by ranking the salaries by person and date with a CTE and the ROW_NUMBER() function. This will put the most recent salary by person in descending order in the first position, which we can filter for later (where rank = 1). After that, it becomes a simple LEFT JOIN from Person to the aliased CTE:

WITH RankedSalaries AS
(
    SELECT 
        PersonId
        ,Date
        ,Salary
        ,ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY Date DESC) AS RowNum
    FROM 
        Salary
)
SELECT 
    p.PersonId
    ,p.Name
    ,p.Surname
    ,s.Date
    ,s.Salary
FROM
    Person p
LEFT JOIN
    RankedSalaries s
    ON
    p.PersonId = s.PersonId
WHERE
    s.RowNum = 1

Alternatively, you could take the contents of the CTE and move it in between the parenthesis of the query you started (i.e. LEFT JOIN (<CTE query>)). Just remember to add the = 1 constraint.