• I like Luis' answer. A different approach, likely with a different query plan which may be more or less efficient depending on your data/indexes, etc is this:

    SELECT a.id,

    a.first_name,

    a.last_name,

    b.income,

    b.department

    FROM CUSTOMERS a

    CROSS APPLY (SELECT TOP 1 b.income, b.department FROM department b WHERE b.id = a.id ORDER BY b.income DESC) AS b