Hi all

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

I tried the following query but GROUP BY does not allow MAX()
SELECT lPriceListsFixedItem.*,
mItems.cDesc,
mItems.cPack,
mItems.cCode
FROM lPriceListsFixedItem
LEFT JOIN mItems
ON lPriceListsFixedItem.iItemID = mItems.iID
WHERE lPriceListsFixedItem.iPID = 69
-- AND lPriceListsFixedItem.iItemID IN (SELECT lPLFI.iItemID FROM lPriceListsFixedItem lPLFI GROUP BY MAX(lPLFI.iBatchNo))
ORDER BY lPriceListsFixedItem.iPID, mItems.cDesc, mItems.cPack, mItems.cCode

Please advise

Thanks and regards
Bhavbhuti