Message info
 
To:firebird-support@yahoogroups.com From:venussoftop Subject:[firebird-support] Latest entries on either Date or Batch No. field Date:Thu, 12 Apr 2012 15:45:09 -0000
 

 

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

__._,_.___
Recent Activity:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
.

__,_._,___