Sean wrote:

> I just came across a query:
>
> SELECT ID FROM table WHERE ID IN
> (SELECT FIRST 2 ID FROM table)
>
> (ID is primary key. The table has 10 records)
>
> How many records does it return, 2 or 10? I got 10, which is the same as
>
> SELECT ID FROM table WHERE ID IN
> (SELECT ID FROM table)
>
> Does "first 2" work in subquery?

As Michael said, it definitely doesn't work on Firebird 1.5 (and in my
opinion you do have a logical error in that there is no ORDER BY in your
subselect - and I doubt ORDER BY is legal in a subselect, so I don't
quite understand how it is supposed to work). Though there are
workarounds, depending on what is your problem, I suppose. I
occasionally do things like

SELECT t1.ID FROM table1 t1
WHERE EXISTS(
SELECT * FROM table2 t2
WHERE t2.ID = t1.ID)
AND NOT EXISTS(
SELECT * FROM table2 t2a
JOIN table2 t2b on t2b.ID < t2a.ID
WHERE t2a.ID < t1.ID)

which basically means 'check that my t1.ID exists in table2 and that
there doesn't exist two lower values of table2.ID'.

It isn't complex once you understand it, but I do not recommend this
approach when trying to simulate FIRST 250 ;o)

HTH,
Set