I though this was how it always worked in Firebird, though I'm still on Fb 1.5 so it might have been changed later. I simply never use 'FIRST' within a subselect.

Generally, I'd say that your program ought to be changed so that whilst it selected a fixed set of columns, it still would allow other stuff to be inserted into the select (like 'first 10').

If that's not feasible, it could still be possible to get the same result without using FIRST 10, but depending on the potential result set, it could be too slow.

Suppose the original select that was wanted was

select first 10 custkey from customer order by custkey

then the following select should eventually give the identical result if custkey was unique:

select c1.custkey from customer c1
where (select count(*) from customer c2 where c2.custkey <= c1.custkey) <= 10
order by c1.custkey

Though I would not run this query if I had a million customers...

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Joe
Sent: 15. oktober 2009 06:15
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: "First N" ignored within subselect???



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