How to get values (of rows) of a column in a new column

Question

I am trying to get the due amount from a database. The customer's details are on one table, and details of payments are on another table. The tables are as following: tables, query

In tblPayments, each transaction is registered with a new Id. So, for each customer, I am trying to get the last transaction recorded and get the Credit and Debit for that last transaction.

So far, I have managed to get the last Id (transaction) for each customer using the following query:

SELECT c.CustomerNumber, Name, Surname, p.LastID 
FROM tblCustomer c 
LEFT JOIN 
    (SELECT CustomerNumber, MAX(ID) AS LastID
     FROM tblPayments 
     GROUP BY CustomerNumber) p ON c.CustomerNumber = p.CustomerNumber

However, I don't know how to get the Credit and Debit related to the last transaction, in my case named LastID.

Any help would be much appreciated.

Answer 1

If I understand correctly, one method uses outer apply:

SELECT c.*, p.*
FROM tblCustomer c OUTER APPLY
     (SELECT TOP (1) p.*
      FROM tblPayments p
      WHERE c.CustomerNumber = p.CustomerNumber
      ORDER BY p.id DESC
     ) p;