Subject Re: [firebird-support] Problem with FIRST in where clause
Author Svein Erling Tysvaer
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
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).


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:
> IX_MEET_LASTCHANGED is a descending index on LASTCHANGED
> cu Christian