SQL tips for ’select limit’ in Firebird DB

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: