Joe,
try changing your SQL to

select prodprice,proddesc,
(select first 1 fifocost from fifocosts B
where B.fifobarcode = A.prodbarcode
and not exists(select 1 from fifocosts C
where C.fifobarcode = B.fifobarcode
and C.fifodate < B.fifodate))
from products A
where PRODBARCODE = '000103'

This will get you one random fifocost out of all records with the
earliest fifodate for PRODBARCODE '000103'. If fifodate is unique for
each fifobarcode in fifocosts you can skip 'first 1'.

It does not surprise me that you cannot order by a field not in the
output set - logically I don't think it makes sense (though Firebird
does not use my brain in any way and may well disagree with me).

HTH,
Set

--- In ib-support@yahoogroups.com, Joe Martinez <joe@j...> wrote:
> The following query works fine:
>
> select prodprice,proddesc,(select first 1 fifocost from fifocosts B
where
> B.fifobarcode = A.prodbarcode)
> from products A
> where PRODBARCODE = '000103'
>
> But, when I add an Order By in the subselect, as follows:
>
> select prodprice,proddesc,(select first 1 fifocost from fifocosts B
where
> B.fifobarcode = A.prodbarcode order by fifodate)
> from products A
> where PRODBARCODE = '000103'
>
> I get a "Token Unknown" error on the word "order".
>
> Why can't I do this?
>
> I want to get the record with earliest date (fifodate). How can I
do this
> correctly?