SQL Server – How to inner join a table to receive just one row of that table where a field is MAX. For example, has the greatest date.

This SQL statement will produce one row for each record found on the account_table for a given account number

We want also want to join the name and address table to the row. We only want one row from the transaction table (there are many)

that has the greatest transaction date and add it to our row as TransactionDate.

Here is how:

SELECT a.account_number, b.name_last, b.name_first, c.address, c.phonenumber, TransactionDate
FROM account_table a
LEFT OUTER JOIN
    (
    SELECT MAX(transaction_date) AS TransactionDate
    FROM transaction_table
    GROUP BY transaction_number
    ) groupedtt ON a. account_number = groupedtt. account_number 
LEFT OUTER JOIN name_table b on a. account_number = b. account_number
LEFT OUTER JOIN address_table c on a account_number = c. account_number 
where a. account_number = 'ACCT12345'