SQL To Retrieve 2 Most Recent Items?

[(PeteCresswell)]

Got a table: "tblPayment" that has payment amounts (in dollars) by TrancheID.

A given Tranche may have thrown off 100 payments or no payments at all.

For those that have at least one payment, I need to retrieve the most recent 1
or two payments and, if 2, average them so that we wind up with three columns:
Payment#1, Payment#2, and Average.

We've got a !PaymentDate field.

If I can do it all in SQL instead of beating down a .Recordset in VBA, I'd
prefer that - if only for my own edification.

Anybody know how this is done?

 

[(PeteCresswell)]

Per Ken Sheridan:

SELECT
P1.TrancheID,
P1.Payment AS [Payment#1],
P2.Payment AS [Payment#2],
(P1.Payment+P2.Payment)/2 AS AveragePayment
FROM tblPayments as P1, tblPayments AS P2
WHERE P1.PaymentDate =
(SELECT MAX(PaymentDate)
FROM tblPayments AS P3
WHERE P3.TrancheID = P1.TrancheID)
AND P2.PaymentDate =
(SELECT MAX(PaymentDate)
FROM tblPayments AS P4
WHERE P4.TrancheID = P1.TrancheID
AND P4.PaymentDate <
(SELECT MAX(PaymentDate)
FROM tblPayments AS P5
WHERE P5.TrancheID = P1.TrancheID))
UNION ALL
SELECT
TrancheID,
MAX(Payment),
NULL, NULL
FROM tblPayments
GROUP BY TrancheID
HAVING COUNT(*) = 1;

Worked like a charm.
I am *truly* impressed!

One question: what does "ALL" do for the query?

Seems like the result sets are the same with or without ALL - albeit in
different sequences.

Something about efficiency?

 

[MikeB]

I too would like to add a "Wow!" for that SQL query. That *is*
impressive!

Mike

Per Ken Sheridan:

SELECT
P1.TrancheID,
P1.Payment AS [Payment#1],
P2.Payment AS [Payment#2],
(P1.Payment+P2.Payment)/2 AS AveragePayment
FROM tblPayments as P1, tblPayments AS P2
WHERE P1.PaymentDate =
(SELECT MAX(PaymentDate)
FROM tblPayments AS P3
WHERE P3.TrancheID = P1.TrancheID)
AND P2.PaymentDate =
(SELECT MAX(PaymentDate)
FROM tblPayments AS P4
WHERE P4.TrancheID = P1.TrancheID
AND P4.PaymentDate <
(SELECT MAX(PaymentDate)
FROM tblPayments AS P5
WHERE P5.TrancheID = P1.TrancheID))
UNION ALL
SELECT
TrancheID,
MAX(Payment),
NULL, NULL
FROM tblPayments
GROUP BY TrancheID
HAVING COUNT(*) = 1;

Worked like a charm.
I am *truly* impressed!

One question: what does "ALL" do for the query?

Seems like the result sets are the same with or without ALL - albeit in
different sequences.

Something about efficiency?