[firebird-support] Latest entries on either Date or Batch No. field

[venussoftop]

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