|Subject||RE: [firebird-support] How do I read a specific range of records from a table in the database|
|Author||Svein Erling Tysvær|
>Svein,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.
>I need unsorted data in the order it was stored so I tried the following and it works exactly as I
>SELECT MyField1, ~ MyFieldn,
>ROWS LastRow-1000 TO LastRow
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,
ORDER BY PK DESC
ROWS 1 TO 1000
That would also be quicker than counting the total number of rows in advance (counting rows take time in Firebird).