--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> 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
>

Hi Set,

Actually this has nothing to do with my previous question about select
first 250 rows. :-) I thought it's a bug so I would like to report it
to you (that's why I started a new thread). Since "first nn" in
subquery is a known issue solved in 2.0, I am totally fine with it.

But, still, thank you anyway!