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?

I'm using FB 1.0.

-Joe