How to Get the SUM Value from Grouped by Last value in a table result

Question

I have this query table,

CREATE TABLE BASE_Purchase
(
PurchaseId BIGINT,
PurchaseDate DATE NOT NULL,
SupplierName VARCHAR(255) NOT NULL,
Amount MONEY NOT NULL,
Outstanding MONEY NULL
PRIMARY KEY(PurchaseId)
)

I have the following result set from the table,

Above image result query ↑

SELECT PurchaseId, PurchaseDate, SupplierName, Amount, Outstanding
FROM BASE_Purchase
ORDER BY SupplierName, PurchaseId

I need the above should be result as follow,

After retrieved the above result and i want to get the SUM of each outstanding

Answer 1

Based on your images, something like this should run on SQL Server 2008.

(It's not completely tested because I didn't want to have to type all that data from your screenprints.)

;WITH _cte
AS (
    SELECT PurchaseId
        ,PurchaseDate
        ,SupplierName
        ,Amount
        ,Outstanding
    FROM BASE_Purchase
    )
SELECT PurchaseId
    ,PurchaseDate
    ,SupplierName
    ,Outstanding
FROM _cte c
WHERE PurchaseDate = (
        SELECT max(PurchaseDate)
        FROM _cte
        WHERE SupplierName = c.SupplierName
        )