Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Using the DBI to return the first ix/i rows

by Anonymous Monk
on Jan 25, 2001 at 16:01 UTC ( #54247=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I need to return the first x rows of a 'select' statement.

$returnRef = $db->selectall_arrayref($sql);

returns all the rows, but the table has >50,000 rows and I only want to see the first 10, for example.

How can this be done?


May you Perl in peace

Replies are listed 'Best First'.
Re: Using the DBI to return the first ix/i rows
by salvadors (Pilgrim) on Jan 25, 2001 at 16:07 UTC

    I need to return the first x rows of a 'select' statement

    The most obvious way is to put a 'LIMIT x' in your SELECT.

    But if for some reason you can't do that, then use something like:

    my $sth = $dbh->prepare($sql); $sth->execute; $sth->bind_columns(\my ($col1, $col2 ... $coln)); my $count = 0; while ($sth->fetch) { [$col1 .. $coln will be populated with the values in that row] last if ++$count == 10; }


      In case you're using Oracle: you don't have a LIMIT SQL command (go figure), so you need to use ROWNUM instead:

      SELECT * FROM table WHERE ROWNUM < 10;

        Sybase doesn't have either of those mechanisms. You need to do it in two steps.

        set rowcount 10 select * from table

        "Perl makes the fun jobs fun
        and the boring jobs bearable" - me

(kudra: see previous answer of question) Re: Using the DBI to return the first ix/i rows
by kudra (Vicar) on Jan 25, 2001 at 16:04 UTC
    You could try looking at this discussion of much the same question. For future reference, you may want to try using the search or the Super Search to see if your question has been asked before.

    If you never want to see more than just 10 rows, you might consider just adding a limit to your query.

Re: Using the DBI to return the first ix/i rows
by dsb (Chaplain) on Jan 25, 2001 at 20:15 UTC
    Instead of doing a 'selectall' try sending an actual SQL query to the database. When you do, use the 'LIMIT' operator to keep the number of rows returned down. Then you could use any number of ways to assign the data to variables. When dealing with more than one column of data I prefer to use 'fetchrow_array' or 'fetchrow_arrayref'. Example:
    #!/usr/bin/perl use DBI; $dbh = DBI->connect( #your database info here# ); $sth = $dbh->prepare( "SELECT * FROM table_name LIMIT x" ); # prepar +es a query to send to DB where 'x' is the number of rows you want ret +urned $sth->execute(); # execute the query against the db while ( ($col1, $col2) = $sth->fetchrow_array() ) { # code # code # code....etc. } $sth->finish(); $dbh->disconnect();
    Hope this helps. - kel -

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://54247]
Approved by root
[choroba]: oh, jet2 didn't show in my search as it only flies on Mondays and Fridays
[choroba]: so, now I need to figure out what to talk about at TPCiG

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (4)
As of 2018-02-23 10:42 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (301 votes). Check out past polls.