Alan,

Thanks for the reply!

>This is one way:

>SELECT FIELD1, FIELD2,
>(SELECT FIRST 1 FIELD1 FROM DETAILTABLE WHERE DETAILTABLE.FK=MASTERTABLE.ID
>ORDER BY SOMETHING) DETAILFIELD1 ,
>(SELECT FIRST 1 FIELD2 FROM DETAILTABLE WHERE DETAILTABLE.FK=MASTERTABLE.ID
>ORDER BY SOMETHING) DETAILFIELD2
>FROM MASTERTABLE WHERE <mastertable row selection criteria>

I had thought of this, as I had gotten sub-select for the first field to work, and have been using a second select to retrieve the remainder of the fields that I need from the detail record. However, my concern was for the execution plan: Is the firebird server optimiser smart enough to recognize that the second sub-select is getting the same record as the first, and just using the record it had already retrieved, or is it going to retrieve the record again? I ask because this example only retrieved two columns from the detail record, but the real-life code retrieves quite a few columns, and I worried about impacting the execution time of the select.

For example, the execution plan for the example you provided looks like this:

PLAN (D INDEX (MASTERID_INDEX))
PLAN (D INDEX (MASTERID_INDEX))
PLAN (M INDEX (MASTER_STOCK_INDEX))

This made me think that it was indeed performing two searches for the detail record.

Thanks,

Dan.