Oh, ok. I wasn’t aware that Firebird re-uses space that way. I need to find
out how the data is stored and deleted which is done by another program I am
unfamiliar with.

I only have access to a test database for my development. There is a
date/time field in the table I am trying to use so I can ORDER BY that
field.



Thanks for the additional information.



Rudy



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Tuesday, January 22, 2013 7:56 AM
To: 'firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] How do I read a specific range of records
from a table in the database

>Svein,
>
>I need unsorted data in the order it was stored so I tried the following

and it works exactly as I

>wanted.
>
>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
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).

Set





[Non-text portions of this message have been removed]