In general, I doubt using FIRST in a subselect works (I don't think that
is what it was designed to do)! The plan is as good as Firebird
currently gets it, the subselect is executed once for every row in MEET
(which is another good reason to avoid using FIRST in the subselect) and
then there is no faster way than to go natural.

Thirdly, your query is ambiguous. You don't specify whether MEETID
belongs to the main MEET or the subselect MEET, so in theory your query
could be comparing MEET.MEETID to itself, which will always return true.

You could try

select * from MEET M1
where M1.MEETID in (select first 50 M2.MEETID from MEET M2 order by
M2.LASTCHANGED
desc)
order by M1.ENDDATE

Failing that (I suspect it doesn't work), try something like

select * from MEET M1
where (select count(*) from MEET M2 where M2.LastChanged >
M1.LastChanged) < 50
order by M1.ENDDATE

If your table is rather big, think of a better way to write what you
want, write a stored procedure or wait for Firebird 2.0 with it's SELECT
FROM <subselect> - which I expect to work with FIRST).

HTH,
Set

Christian Kaufmann wrote:

> The following query should find the last 50 meets, that where recently
> changed and should sort the result by the date of the meet:
>
> select * from MEET
> where MEETID in (select first 50 MEETID from MEET order by LASTCHANGED
> desc)
> order by ENDDATE
>
> When I run this query, I get all records of my table MEET. Can
> somebody tell me, what goes wrong?
>
> Also the plan is not perfect:
>
> PLAN (MEET ORDER IX_MEET_LASTCHANGED)
> PLAN (MEET NATURAL)
>
> IX_MEET_LASTCHANGED is a descending index on LASTCHANGED
>
>
> cu Christian