>Hi all
Hi again, Bhavbhuti.
>I have Parent-Child tables that contain Price List and are updated with the
>same items
>in them but with either a later date or a later batch no. filled in, so
>typical child
>records would be as follows
>
>iID, iItemID, bRate, dDt, iBatchNo
>1, 123, 50.00, empty, 156
>2, 123, 55.00, empty, 160
>3, 889, 125.00, 01/01/2012, empty
>4, 889, 130.00, 15/03/2012, empty
>5, 889, 135.00, 05/04/2012, empty
>6, 1010, 5.00, empty, empty
>7, 555, 521.00, 01/02/2012, empty
>8, 756, 20.00, empty, 765
>9, ...
>
>Now I want to only report back the latest of these rows, so the records I am
>looking
>for are just these, please note the singletons with iID 6, 7 and 8
>2, 123, 55.00, empty, 160
>5, 889, 135.00, 05/04/2012, empty
>6, 1010, 5.00, empty, empty
>7, 555, 521.00, 01/02/2012, empty
>8, 756, 20.00, empty, 765
First, I would generally recommend you to forget that it is at all possible to
use IN (<subselect>). Myself, I occasionally use IN (constant), but hardly ever
IN (<subselect>) - using EXISTS can normally replace it, is sometimes faster
and never slower (well, I don't know about the later Firebird versions, but
would expect there to still be queries that Firebird doesn't convert from IN
(<subselect>) to EXISTS under the hood).
I do not even quite understand your query, I have no clue why you put
lPLFI.iBatchNo in it. Below is how I would have tried to get item 2, 5, 6, 7
and 8, note that the logic is changed from directly asking from the latest
version to asking for those that doesn't have a later version (just a subtle
change in the way of thinking, the result is the same):
SELECT lPLFI.*,
mI.cDesc,
mI.cPack,
mI.cCode
FROM lPriceListsFixedItem lPLFI
LEFT JOIN mItems mI
ON lPLFI.iItemID = mI.iID
WHERE lPLFI.iPID = 69
AND NOT EXISTS(SELECT * FROM lPriceListsFixedItem lPLFI2
WHERE lPLFI.iItemID = lPLFI2.iItemID
AND lPLFI.iID < lPLFI2.iID)
ORDER BY lPLFI.iPID, mI.cDesc, mI.cPack, mI.cCode
HTH,
Set
