>>Hello!! I just need to select the last occurence of the field "UNITARIO" and
>>it's ID
>>
>>This does the job, but I wander if there is a better option.
>>
>>ID and ID_STOCK has indexes, and the plan reports that they are being used
>>
>>SELECT
>> F.UNITARIO, F.ID
>>FROM
>> FAC_COM_DETALLE F
>>WHERE
>> (F.ID_STOCK=:ID_STOCK)
>> AND
>> (F.ID = (select MAX(D.ID) from fac_com_detalle D WHERE D.id_stock =
>>:ID_STOCK))
>
>I have a similar requirement, and have found that replacing
>
>select MAX(D.ID) from fac_com_detalle D WHERE D.id_stock = :ID_STOCK
>
>with
>
>select first 1 D.ID from fac_com_detalle D WHERE D.id_stock = :ID_STOCK or by D.ID desc
>
>can actually be faster (but does seem to defy logic for being faster...)

I would typically do

SELECT F.UNITARIO, F.ID
FROM FAC_COM_DETALLE F
WHERE (F.ID_STOCK=:ID_STOCK)
AND NOT EXISTS(
SELECT *
FROM FAC_COM_DETALLE D
WHERE F.ID_STOCK = D.ID_STOCK
AND F.ID < D.ID)

but then, I am on Firebird 1.5.

With Firebird 2.x, it would be interesting to learn whether

WITH MyMax(MaxID) as
(SELECT MAX(ID)
FROM FAC_COM_DETALLE D
WHERE F.ID_STOCK = :ID_STOCK)

SELECT F.UNITARIO, F.ID
FROM FAC_COM_DETALLE F
JOIN MyMax M on F.ID = M.MaxID
WHERE F.ID_STOCK=:ID_STOCK

is any quicker (you can replace the MAX with SELECT FIRST 1 or SELECT .. ROWS 1 as Maya suggested if you want). Oh, and ID needs a DESCENDING index, the default ASCENDING is not sufficient for this query.

Set