--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>

> Secondly, when you do self-referencing subqueries, you must use relation aliases to separate the outer set from the inner set.
> e.g.
> select c1.custkey from customer c1
> where c1.custkey in
> (select first 10 c2.custkey from customer c2
> where c2.something = c1.something ....
> order by c2.whatever )
>
> So try correcting that syntax, and see whether the results come closer to your expectations.

No, I just tried it, and get the same results as before.

> On the other hand, if your example is similar to what you actually want to do, I wonder why you need the self-referencing subquery at all. If you just want the first 10 custkey values, why not get them directly?

Here is the reason:

Our application allows the users to write "ad hoc" queries against certain tables in the database, the results of which go into a fixed-structure dataset that they can scroll through, and on which the application will also later perform other functions. However, in order to make sure that the user selects the correct columns (otherwise it would mess up other later program logic), we hard-code the beginning of the query as:

select (field list) from (tablename)

We had a user who wanted to do a "first n" query. However, since the "first n" part has to go in the middle of the hard-coded part of the query, they couldn't do it. I was going to suggest something like my example as a workaround, until I found out when I tested it that it didn't work :-(.

-Joe

>
> select first 10 custkey from customer
> where something = aValue /* or ? for a parameterised search input */
> order by whatever
>
> ./heLen
>