I tried it with an SP, and it seems to work!

My SP looks like this:

CREATE PROCEDURE SP_TEST1 (st_id bigint)
returns (m_id bigint, m_field1 bigint, m_field2 bigint, m_field3 integer, m_field4 integer,
d_id bigint, d_field1 integer, d_field2 integer)
AS
BEGIN
FOR SELECT m.id, m.field1, m.field2, m.field3, m.field4
FROM mastertable m
WHERE m.id=:st_id
INTO :m_id, :m_field1, :m_field2, :m_field3, :m_field4
DO
BEGIN
FOR SELECT first 1 id, field1, field2
FROM detailtable d
WHERE d.masterid=:m_id
ORDER BY field2
INTO :d_id, :d_field1, :d_field2
DO
BEGIN
suspend;
END
END
END!!

Adding the "order by" clause, as you recommended, seems to have slowed down the select (testing on a small test database, not the live database), and I suspect that Firebird is doing an additional sort, although I know of no way of confirming this inside an SP. The "field2" clause is actually an integer representation of a date, and all detail records were inserted in date order (the application inserts data in batches, by a single process, and no other process ever inserts records into these tables). I also created an index for the "masterid" field, and that is the index used if I don't include the order by clause. Thus, I am very tempted to leave the "order by" out, and let it use the masterid index. As long as I never get records out of date order, I should be fine. If they do get out of date order, I will, of course, get unexpected and unpredictable results.... However, the actual detail table currently has upwards of 16 million rows (expected to grow to several hundred million over the next year), with the average master record having tens of thousands of detail records, so I really really don't want Firebird trying to do a sort of many thousands of detail records for every row of the master table if I don't have to.

Unless you or someone else sees something dreadfully wrong with this, I'm going to continue down this path, as this seems like a much better solution than the multiple sub-selects or my original solution involving two separate selects.

Regards,

Dan.