a friend asked me about how to do the similar things like this one (of MySQL), but in Firebird DB:
select limit ... from foo
something like that. and as far as I know, the equivalent SQL should be like this one, at least this works in Firebird 1.5:
select first [totalRowsToDisplay] [skip [totalRowsToSkip]] * from foo
so, for example I want to select the first 15 rows from table TSRDet, it would be served by this SQL selection:
select first 15 * from TSRDet
but now I want to skip the first 3 rows, and display the next 15 rows from the same table. how should I do that? well, just add the previous ‘skip [totalRowsToSkip]’ things:
select first 15 skip 3 * from TSRDet
pretty much simple right? and now I only want some columns to be displayed:
select first 15 skip 3 SRCode, Num, ProductID, QTY, Price from TSRDet
quite simpler than we thought, right? I hope this helps. 🙂
added October 6, 2009:
sorry for long inactivity due to illness.
a visitor (Csaba Gabor) kindly made a question in the comment section, asking this:
by Csaba Gabor:
suppose you want to show the last 15 rows (not including the final 3 rows) in the table.
I am essentially asking here about negative values for First (and or Skip) or reversing the default ordering.
my answer suggestion:
it depends on which column you want to index, in this case I picked up the SRCode column.
and basically there are two solutions, I guess (CMIIW). this is the first (common) suggestion:
select first 15 skip 3 SRCode, Num, ProductID, QTY, Price from TSRDet order by SRCode desc
but it will display the rows in SRCode descending order. you might dislike the result’s ordering.
so if we want to keep SRCode in ascending order for the rows, then what comes to my mind is something similar like this one.
select SRCode, Num, ProductID, QTY, Price from TSRDet where SRCode in (select first 15 skip 3 SRCode from TSRDet order by SRCode desc) order by SRCode
wonder if this answers your question. :thinking: