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

A date/time field won't help in case of a DELETE, because the record is
gone when it is deleted. ;-)

You might consider a trigger-based server-side change data capture
mechanism, which inserts log records into a separate table. The beauty
of a trigger-based solution is, it doesn't force you to change the
client code.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

> 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]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>