Helen et al,

TOP n isn't a major issue, in fact it could be implemented very easily....
cue small digression.

GDML (the native precursor to SQL) in InterBase supported a FIRST n syntax,
but when 4.0 was implemented and a decision was made to support SQL more
effectively than it was in pre 4.0, the InterBase lead engineer at the time
for SQL (no names) at that time made a decision that the only SQL that would
get supported was ANSI SQL the standard, nothing more, nothing less. As such
a collection of useful capabilities that InterBase had, disappeared, unless
you had access to QLI. QLI wasn't shipped with 4.0 on Windows, so there are
a large number of users out there who have no idea that InterBase supports
all sorts of weird and wonderful capabilities....

Some examples are cross database joins (nested for loops), cross database
transactions, FIRST n etc, the code for this exists in the engine at the
API, and BLR levels, all we have to do is add the DSQL interface. Over the
last 12 months or so a number of us wanted to re-surface some of these
missing capabilities via SQL, 6.0 was the start (for example the ability to
modify columns and domains on the fly). I for one would like to see the rest
of QLI's functionality (where appropriate) re-surfaced at the SQL level.

The issue of whether FIRST n should be used for particular applications or
not, is not one I would wish to comment on, but FIRST n was absolutely
superb when it came to prototyping queries on large data sets.....

Regards
Paul

>But what about performance?
>How fast is a subquery compared with an iterative
>loop in a stored procedure?
>This was the idea behind my asking for TOP function
>if it´s implemented in servers standard functions it could be faster?

TOP isn't standard SQL and it's not a very sensible kind of thing to ask of
a Client/Server database, given that the data isn't stored in any physical
order. I'd be surprised if got anywhere near the top of anyone's list of
"must-haves" for InterBase.

As to your question, I find anything using subqueries to be a very slow way
to fill a column in a dataset. But, in the case where you are fetching a
tiny dataset, it could be cheaper. Why not try it out and let us know the
difference? Don't forget - I'm trawling for wisdom like this for the
Gotchas section of the IBDH.

Paul Beach
Vice President Sales & Marketing
InterBase Software Corporation
Tel (USA):+1 831 431 1097
Tel (UK):+44 (0) 1844 354465
Mobile: +44 (0) 7887 890375
E-mail: pabeach@...