peppiv has asked for the wisdom of the Perl Monks concerning the following question:

Good day y'all!

I need to limit the results of a DBI query. But here's the catch. I'm querying a SQL Server DB which does not use the LIMIT command commonly found in MySQL. Oh, if it could be that easy.

Here's the basic structure:

my $sth = $dbh->prepare("SELECT DISTINCT country, COUNT(*) FROM contac
+ts WHERE country != '' GROUP BY country ORDER BY 2 DESC"); 
     $sth->execute() or die $sth->errstr; 
     while (my @result = $sth->fetchrow_array()) { 
     print qq($result[0], $result[1]);
}
$sth->finish();

[download]

I get the return of the country and the count. But it returns all rows and I only need the first 10 - in order of descending count value.

Any help would be greatly appreciated.

I tried to put together a for statement with a count in it but that was a disaster.

TIA

peppiv

Replies are listed 'Best First'.
Re: Limit results of a DBI query
by Fletch (Bishop) on Jan 28, 2003 at 16:51 UTC 
    my $rows = 0;
while( my @result = $sth->fetchrow_array() ) {
  last if $rows++ >= 10;
  ...
}

$sth->finish();

    [download]
[reply]
[d/l]
      Ah Fletch, you da man! Works like a charm.

      Sooopa Thanx

      peppiv

[reply]
Re: Limit results of a DBI query
by mpeppler (Vicar) on Jan 28, 2003 at 16:36 UTC
    I think MS-SQL server understands the TOP directive, as in 
    select TOP 10 distinct country ...

    [download]
    Alternatively, issue a 
    $dbh->do("set rowcount 10");

    [download]
    first. This will limit all queries to 10 rows returned on that connection, until it is reset. You set it back to "unlimited" with 
    $dbh->do("set rowcount 0");

    [download]
    Michael
[reply]
[d/l]
[select]
      Thanks for the help.

      The TOP 10 command didn't work and I've tried putting the "set rowcount" statement in various places but I couldn't get it to work either.

      Any other ideas?

      peppiv

[reply]
        The "set rowcount" directive applies to physical connection. If for some reason the $dbh->do() command opens a new connection under the covers then it won't work.

        Which DBD driver are you using?

        Michael

[reply]
Re: Limit results of a DBI query
by SysApe9000 (Acolyte) on Jan 28, 2003 at 17:49 UTC

    I think the variable rownum could be used for this too, if your database supports it. Basically every result returned is assigned a rownum and you can can use it like: 

    select * from sometable where rownum < 10;

    [download]

    This should select only the first 10 (subject to an off-by-one error, as I don't know if rownum starts at 0 or 1) records that would have been returned by this query.

    Of course, simply using the cursor and ending the query after retreiving the first 10 results will also work, as would limit if your system supported it. By using limit or rownum you can still use the very handy selectall_* routines, though.

[reply]
[d/l]
Re: Limit results of a DBI query
by Ryszard (Priest) on Jan 29, 2003 at 07:08 UTC
    A neat trick in Oracle (I dont know if MS-SQL supports "rownum" (or inline views)) is 
    select column1 
  from (
        select column1, rownum
          from table 1
       )
 where rownum > ? and rownum < ?

    [download]
[reply]
[d/l]
Re: Limit results of a DBI query
by Anonymous Monk on Jun 27, 2009 at 16:15 UTC

    on SQL Server,

    you can used function row_number() over (..) bellow example:

    select ROW_NUMBER() OVER ( order by column_name ) as myrow, column1, column2, … from TEST1 where myrow between 5 and 10 ;

    note: column_name is column that you need to order.

    see more detail at

    Blog IT Knowledge Indy : SQL Tips

    http://blogitknowledge.u-indy.com/database-services/sql-tips/sql-tips-how-to-limiting-result-rows-many-databases/

[reply]

Back to Seekers of Perl Wisdom