Subject Re: [ib-support] SQL - getting first and last row
Author Jaume Andreu Sabater Malondra
On Tuesday 10 September 2002 15:50, tickerboo2002 wrote:

> Is it possible to get the first and last rows in one query? i.e. the
> highest and lowest ID, or the earliest and latest timestamp - either
> method should return the same rows.

First row: SELECT FIRST (and specify the criteria, i.e. the ORDER BY
clausule). The first ID would be SELECT FIRST ID_FIELD FROM TABLE ORDER BY
ID_FIELD. About time and dates, the same putting time and date fields instead
of Id fields. And, of course, it will not return the same rows depending on
the ORDER BY clausule.

Finally, you also have the aggregate MIN and MAX functions, but note that they
are not the same that a FIRST.

About last row, I don't know. Maybe someone else can help. Maybe there's a
LAST clausule which can be applied to SELECT.

> ....also, I assume I would need another query to get the row count
> (Select count(*) from myTable) - is this the most efficient way of
> getting the row count?

Well, you could have the count(*) stored in any other table or somewhere and
update it by using triggers. Otherwire, you need to launch a SELECT COUNT(*)

Jaume Andreu Sabater Malondra
Registered linux user #209072

"Ubi sapientas ibi libertas"