--- In [email protected], Svein Erling Tysvær <svein.erling.tysvaer@...> wrote: > 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 >
Somehow IN comes to my mind first, I am at an intermediate stage where I am neither a beginner nor a pro with SELECTs :) Thanks for the query (the other reply) it worked perfectly as wanted. Thanks again. Kind regards Bhavbhuti
