Subject RE: [firebird-support] How do I read a specific range of records from a table in the database
Author Svein Erling Tysvær
>I need unsorted data in the order it was stored so I tried the following and it works exactly as I
>SELECT MyField1, ~ MyFieldn,
>FROM MyTable
>ROWS LastRow-1000 TO LastRow

Do you ever update or delete rows in MyTable, Rudy? If you only INSERT, then you may be lucky and get the last inserted rows (though it is not guaranteed), but if something has been changed, then Firebird will reuse the space that the changed/deleted rows occupied and newly inserted records are likely not to be among the "last" 1000 rows.

Logically, Firebird returns sets of records and if there is no ORDER BY, then the order is random. Although I'm not surprised that the rows you inserted last happens to be the ones that are returned in the statement above, don't rely on it. If you care about which rows are returned, you MUST use ORDER BY before ROWS.

From what you've described so far, I'd recommend you to add another field containing a primary key (lets call it PK and assume it is an integer), add a descending index on that field and populate it through a BEFORE INSERT trigger. Then use

SELECT MyField1, ~ MyFieldn,
FROM MyTable
ROWS 1 TO 1000

That would also be quicker than counting the total number of rows in advance (counting rows take time in Firebird).