Ooh … Begitu! I see, I see …

Icon

Setelah kelayapan di internet, cari-cari info dari sana-sini dan bongkar pasang tips (terutama programming), akhirnya baru sadar … ternyata begitu caranya … [all about tricks and tips I have found during my work as programmer.]

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:

Advertisements

Filed under: Firebird

4 Responses

  1. Csaba Gabor says:

    Nice example, thanks. But 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.

    • oohbegitu says:

      hi! sorry for long inactivity due to illness. just added some codes in the original post. wonder if it will answer your question.

      thanks for your inspiring comment and question. 🙂

  2. InvarBrass says:

    Just to add: From FB 2.x you could use the “ROWS” keyword to the same effect.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives

%d bloggers like this: