At 10:47 AM 07-02-01 +0200, you wrote:

>Hi,
>
>I need to retrieve the first row of a table. Using Microsoft SQL Server 7.0,
>this can be done with the following SET statement...
>
>SET ROWCOUNT 1
>SELECT * FROM EMPLOYEE
>
>How do I do something similar with IB?

There is no such thing as the "first row of a table". @@ROWCOUNT in
SQLServer is a global variable (aaaargh!!!) that you can mess around with
in stored procs to count and store the number of rows affected in a
query. SET ROWCOUNT is a vendor-specific "fudge" to eliminate all but one
row - but it's not necessarily going to be "the first row ever created" -
only the first row in the set. In your example, it will thus be the first
row returned by SELECT * FROM... with no limiting criteria.

Normally, if you want the "first" row of a set you need to specify some
sort of ordinality and ask for the "minimum" (usually) value of a column
that has some measure of ordinality.
For example, if your primary key (e.g. PK_ID) is a generator value, you can get

SELECT MIN(PK_ID) FROM ATABLE

or, if you have a date column, you could get

SELECT MIN(DATE_JOINED) from ATABLE.

When InterBase constructs a set, it applies an internal ordinality column
to it that is reliable as long as the transaction lasts. This column is
named RDB$DB_KEY. You can't access it in a dynamic SELECT statement (at
least I don't think so) but you can refer to it when you have the dataset
in transaction. It can be useful to play around with in triggers and
stored procedures, e.g. iterate through a FOR...SELECT loop, read
RDB$DB_KEY and store its value in a variable, thus *kind of* mimicking
SQLServer's RowCount, as long as you are **quite clear** that it is
meaningless outside of the current active transaction.

Claudio Valderrama wrote a paper about it - see http://www.cvalde.com

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________