3

I'm using firebird database and it does not seem to have ROWNUM or ROW_NUMBER() like sql server does so I cannot follow the procedure explained here.

I have a query whose result dataset is bigger than what my system memory can accommodate. I'm trying load the dataset in smaller chunks. I have a series of generic queries which I cannot modify and they could be anything. I know I can do

select first 5000 * from
(-my actual query here-)

to get the first 5000 records. But how can I go about getting the next 5000 records.

Thanks

Community
  • 1
  • 1
Professor Chaos
  • 8,850
  • 8
  • 38
  • 54

2 Answers2

8

Since FireBird 2.0 ROWS syntax is supported, ie you would use

select * from tab ROWS m TO n

I suggest you download FB's language reference update where it is documented.

ain
  • 22,394
  • 3
  • 54
  • 74
3

In firebird you use Select First ? Skip ? to specific how many, and what your offset is.

g.d.d.c
  • 46,865
  • 9
  • 101
  • 111
  • OH MY! Didn't think it would be this simple. this changes everything! thanks – Professor Chaos Jul 18 '11 at 19:07
  • Be careful using `first` in subqueries. There is a [documented bug](http://tracker.firebirdsql.org/browse/CORE-634) that is not fixed in 2.1. – Allan Jul 18 '11 at 19:10
  • 1
    In Firebird 2.0 and up the SQL-compliant ROWS syntax is available and should be used instead of the `FIRST ... SKIP` – ain Jul 18 '11 at 19:15
  • @ain unless you want Select * skip 5000 from table. You cannot duplicate that behavior with rows syntax. Not very common anyway. – EMBarbosa Jul 20 '11 at 00:46
  • 1
    @EMBarbosa You kind of can; quote from the doc: "You can often “fake it” though, by supplying a second argument that you know to be bigger than the number of rows in the set". – ain Jul 20 '11 at 08:53