Subject Re: [ib-support] Select first row
Author Helen Borrie
At 10:47 AM 07-02-01 +0200, you wrote:
>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...
>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


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


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


All for Open and Open for All
InterBase Developer Initiative ยท