At 12:43 AM 19-11-01 +0100, you wrote:

>Thanks, for answering. even if I was posting this on the wrong list! Sorry
>for that. I will be more careful next time. I was running this sql in a
>TIBQuery and wanted to tell about this too. Didn't realised that I in fact
>posted a sql specific question, after formulating my problem.
>
>One question dough, didn't you mean SELECT *LAST* APP_DATETIME,
>APP_SERVICEID ?

No. Notice that the set was ordered in DESC(ending) order.

>Is this pure SQL code? Or a special FireBird thing?

It is standard SQL 3 for selecting the first n items from an ordered set. Non-standard RDBMSs implement TOP and/or LIMIT to get similar sets. FIRST m SKIP n isn't in the SQL-92 standard. It first appears in SQL-3, so that is what Firebird implemented.

There really is no concept of a physical "first" and "last" in a RDBMS. There are no physical "tables". Rows belonging to the entity you define as a table get stored together on a page, but that page can be stored anywhere at all in the physical file. Multiple pages containing TableA rows are not stored contiguously on disk, either.

Therefore, the language doesn't need both FIRST and LAST. You simply have your SQL organise the output rows so that they are ordered on the column you want to target for FIRST m [SKIP] n. So if you want the MAX of a numeric column (including a date), you output your ordered set to the database cache in descending order on that column and select the FIRST (or FIRST m if you want a set of more than one row).

regards,
Helen

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