SQL Issue: Trying To Return Last 2 Of Something?

[PeteCresswell]

Got a query that I'm trying to return the last two of something with,
but for some entities it is returning two rows.

I concocted a sample application that reproduces the problem.

The app can be DL'd from www.wchs59.com/Playpen.zip.

The query in question is named: "qryBadBoy".

You will need to View | SQL to really see what's going on

For what it's worth, here's the SQL:
--------------------------------------------------------------------------------
SELECT P1.TrancheID, P1.PaymentAmount AS [PaymentAmount#1],
P2.PaymentAmount AS [PaymentAmount#2], ([P1].[PaymentAmount]+[P2].
[PaymentAmount])/2 AS PaymentAmountAverageLast2
FROM qryTrancheFund_Last2Payments_Common AS P1,
qryTrancheFund_Last2Payments_Common AS P2
WHERE (((P1.PaymentDate)=(SELECT MAX(PaymentDate)
FROM tblPayment AS P3
WHERE P3.TrancheID = P1.TrancheID)) AND ((P2.PaymentDate)=(SELECT
MAX(PaymentDate)
FROM tblPayment AS P4
WHERE P4.TrancheID = P1.TrancheID
AND P4.PaymentDate <
(SELECT MAX(PaymentDate)
FROM tblPayment AS P5
WHERE P5.TrancheID = P1.TrancheID))));
--------------------------------------------------------------------------------


I'm way, *way*, WAY over my head with this one - mainly doing a rote
imitation of something similar.

Can anybody shed some light?

 

[PeteCresswell]

Also... when running the query, supply FundID=1.

Then note that TrancheIDs 86, 101, 117, and 132 all suffer from
multiple rows - whereas we want only a single row for each
TrancheID... said row containing the last payment, the second-to-last
payment and an average of those two payments.

 

[PeteCresswell]

Also... when running the query, supply FundID=1.

Then note that TrancheIDs 86, 101, 117, and 132 all suffer from
multiple rows - whereas we want only a single row for each
TrancheID... said row containing the last payment, the second-to-last
payment and an average of those two payments.

 

[Tom Ellison]

Dear Pete:

I haven't done the homework on this. Didn't look at your database. But a
suggestion occurred to me, and since no one else has given it a shot, I
thought I'd throw this out.

If you want query that returns two rows from the entire table, use the the
TOP 2 predicate and order the rows in reverse. That gives you BOTTOM 2 (not
that there is any such SQL construct). After the correct rows have been
selected, you can query that query (either with a separate query or as a
sub-query) and re-order them in the way you want to see them.

If you want to bottom 2 rows in each "group" of records, where a "group" is
a set of rows in which certain columns contain matching data, then some
additional coding would do the trick for you. If you'll supply which
columns form this "group" and which columns supply the sorting order, I'll
try to code that into your SQL for you.

Finally, be aware that finding the TOP (or BOTTOM) 2 rows will not
necessarily return 2 rows. It may return more than 2. Consider this set:

A George
B John
C James
C Susan
C Mary

The bottom two of this set, considering only the initial letter, is not two,
but 3 rows:

C James
C Susan
C Mary

When there is a tie, then more rows may be included than requested. Only
some form of uniqueness will prevent this.

Also, if there is only 1 row, then you won't get 2 rows when you ask for the
last 2. Obvious, eh?

Let me know if this helps, and if I can be of any further assistance
(assuming I have been of some assistance already, which is not a given).

Tom Ellison
Access MVP

Got a query that I'm trying to return the last two of something with,
but for some entities it is returning two rows.

I concocted a sample application that reproduces the problem.

The app can be DL'd from www.wchs59.com/Playpen.zip.

The query in question is named: "qryBadBoy".

You will need to View | SQL to really see what's going on

For what it's worth, here's the SQL:
--------------------------------------------------------------------------------
SELECT P1.TrancheID, P1.PaymentAmount AS [PaymentAmount#1],
P2.PaymentAmount AS [PaymentAmount#2], ([P1].[PaymentAmount]+[P2].
[PaymentAmount])/2 AS PaymentAmountAverageLast2
FROM qryTrancheFund_Last2Payments_Common AS P1,
qryTrancheFund_Last2Payments_Common AS P2
WHERE (((P1.PaymentDate)=(SELECT MAX(PaymentDate)
FROM tblPayment AS P3
WHERE P3.TrancheID = P1.TrancheID)) AND ((P2.PaymentDate)=(SELECT
MAX(PaymentDate)
FROM tblPayment AS P4
WHERE P4.TrancheID = P1.TrancheID
AND P4.PaymentDate <
(SELECT MAX(PaymentDate)
FROM tblPayment AS P5
WHERE P5.TrancheID = P1.TrancheID))));
--------------------------------------------------------------------------------


I'm way, *way*, WAY over my head with this one - mainly doing a rote
imitation of something similar.

Can anybody shed some light?

 

[(PeteCresswell)]

Per Tom Ellison:

The bottom two of this set, considering only the initial letter, is not two,
but 3 rows:

C James
C Susan
C Mary

When there is a tie, then more rows may be included than requested. Only
some form of uniqueness will prevent this.

Also, if there is only 1 row, then you won't get 2 rows when you ask for the
last 2. Obvious, eh?

Let me know if this helps, and if I can be of any further assistance
(assuming I have been of some assistance already, which is not a given).

That's kind of where I am now: too many rows.

To reiterate (or, more likely explain what wasn't explained in
the first place...): I've got a bunch of "Payments". Each
payment is a dollar amount on a certain date which was paid out
by a certain type of bond ("Tranche").

What I need to do is determine the most recent two payments for
each tranche, incorporate them into a single row, and then
compute an average of the two.

The problem is that the solution I have in place has started
returning more than one combined row for some tranches. One of
the "latest" payments in those cases seems tb pointing to an
entirely different tranche.

I'm coming around to thinking I should stick with something I
know/understand.

First thing that comes to mind is a work table from which I
iteratively carve away the rows I don't want. Easy to debug
by virtue of being sequential/compartmentalized.

i.e. populate the work table with all records and then query for
Count(TrancheID) > 2 grouped by TrancheID; identify and delete
Min(PaymentDate) for each of those TrancheIDs... and re-iterate
until there are no rows returned from Count(TrancheID)>2... which
should leave me with the two most recent fewer rows for each
tranche - or a single row for those which only had one payment to
begin with.