--- In firebird-support@yahoogroups.com, 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