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



Thank you for your help



Rudy



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Tuesday, January 22, 2013 2:47 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

>Support,
>
>I am using Firebird server 2.5.2, Windows 7, and Visual Basic 2010.
>
>I would like to read a certain # of records from a table in the database

using a start

>/ stop record range. I am trying to re-write an application written by

someone else

>which reads the entire database from beginning to end which takes a long

time and I may

>only need the last 1000 records of a database that can have over 100,000.
>
>I have been using the following to determine the total number of records in

the

>database so I should be able to use it to establish a range.
>
>'Find the total number of records in the SURFACE table
>Dim count As FbCommand = New FbCommand("SELECT COUNT(*) FROM (
>select * from SURFACE )", fbc)
>Dim readCount As FbDataReader = count.ExecuteReader
>readCount.Read()
>lastDbRecord = readCount.GetValue(0)
>' Get the total number of records in this database

Hi Rudy!

One of the differences between desktop databases like Access and
client/server databases like Firebird, is that desktop databases typically
work with tables, whereas client/server databases work with datasets.
Reading the last 1000 records rather than the entire table is a good start
in this transition, so congratulations!

The best way is to select only records and fields of interest, e.g.

SELECT MyPK, MyInterestingTextField, MyInterestingInteger1,
MyInterestingInteger2
FROM MyTable
WHERE SomeField = :MyParameter
AND SomeOtherField = :MyParameter2
ORDER BY MyDate DESC
ROWS 1 TO 1000

If you want to select the last 1000 rows, but display them in ascending
order, you can do:

with tmp (MyPK, MyITF, MyII1, MyII2, MyDate) as
(SELECT MyPK, MyInterestingTextField, MyInterestingInteger1,
MyInterestingInteger2, MyDate
FROM MyTable
WHERE SomeField = :MyParameter
AND SomeOtherField = :MyParameter2
ORDER BY MyDate DESC
ROWS 1 TO 1000)
SELECT MyPK, MyInterestingTextField, MyInterestingInteger1,
MyInterestingInteger2
FROM tmp
ORDER BY MyDate ASC /*ASC is default, so it is not necessary to include*/

Sometimes users are used to see entire tables (or at least they were before
the Internet) and start to wonder/worry when they only see some of their
data. However, it IS preferable to show only data of interest, no-one
benefits from seeing data irrelevant for their use.

HTH,
Set





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