Querying Select Number of Rows

[Perl Newby]

I am using dbi:odbc and I am trying to grab 11 rows out of my Data Base. I want these eleven rows to be newer than a certian DATE and TIME. But if there are 20 rows newer than the date and time I state, I want to get the oldest 11 rows, ordered by DATE and TIME. I have done this with ASP by using a TOP statement inside a nested select statement together (ordering the nested select asc and the outside select by desc), but I was unable to get this to work in my perl code. I was wondering if there is a way to grab 11 rows, the way I want to (without ordering them by DATE and TIME asc ), because there could be cases when there will be less than 11 rows returned.

use dbi;
  my $dbh, $sth;
  my ($sport, $import) = @_;
  my ($path) = "d:/text/time/" . $sport . ".txt";
  open (text, "$path") or die "couldn't open!\n";
  my (@text) = <text>;
  my $d = $text[0];
  my $t = $text[1];
  my $a = $text[2];
  
  $dbh = DBI->connect("dbi:ODBC:burlee","spacecitysports","houston34")
+ or die die "Can't open connection\n";
  $sth = $dbh->prepare("select *
      from DATA_DB_ENTRY__ASTROS_STAGING
      where TS_DATE  >= '" . $d . "'
      and IMPORTACE = '" . $import . "'
      order by TS_DATE asc, TS_TIME asc
      ");
  $sth->execute();

this is an example of how I was able to get the info queried out but I can not use this if there is less than 11 rows returned.

[Arguile]

As runrig said, syntaxes can vary... so here are some common ones:

PostgreSQL, MySQL, and some other have variants of the LIMIT x OFFSET y clause. This always (to my knowledge) appears as the last clause in the query.

...
ORDER BY field1, field2
LIMIT 10

-- with OFFSET

LIMIT 10 OFFSET 1

-- implicit OFFSET (MySQL)

LIMIT 10,1

In Oracle you can use the "rownum" indexing of the recordset to return a specific number of records.

...
FROM table1 a, table2 b
WHERE rownum <= 10
...

If you're working with an M$ DBMS like SQL Server (I refuse to allow Access to be called a DBMS) or one Informix's you can use the TOP syntax. TOP is part of the SELECT clause as seen below.

SELECT TOP 10 field1, field2, ... ,fieldn
FROM ...

I know nothing of DBI so the next statement is a generality. The benefits to using the limiting at the DBMS side are smaller recordsets transfered back and often quicker queries (DBMS dependant), you do loose a certain amount of portability however. Staying with straight ANSI SQL and datatypes is always a problem though.

[runrig]

This must be What are placeholders in DBI, and why would I want to use them? day :)

Just have your fetch loop exit after 11 rows are fetched. That will give you the 11 oldest rows. It shouldn't matter if there are fewer than 11 rows, then the loop will end without an explicit exit. Queries can sometimes be limited to a certain number of rows, but it is database dependent and the syntax varies.

[chipmunk]

Why can't you use that if fewer than 11 rows are returned? That code will work just fine, regardless of the number of rows.

You want to select the oldest 11 rows (after a certain date), without ordering by date and time... But if you don't order by date and time, how do you know which are the oldest??

To fill out your code a little more:

#!perl -w

use strict;

use DBI;  # note: must be uppercase

my $dbh = DBI->connect("dbi:ODBC:burlee", "username", "password")
  or die "Can't connect to database: $DBI::errstr\n";

{

# $sth is a static variable for get_sport()
# it's private to get_sport(), and keeps its value between calls
my $sth;

# get_sport($sport, $import)
# returns a list of array references
sub get_sport {

  my ($sport, $import) = @_;

  my $path = "d:/text/time/$sport.txt";

  open(TEXT, $path) or die "Can't open $path: $!\n";

  my $date = <TEXT>;
  chomp $date;

  close(TEXT);

  # prepare, if it wasn't prepared before
  $sth ||= $dbh->prepare(<<"  EndOfSQL");
    select *
    from DATA_DB_ENTRY__ASTROS_STAGING
    where TS_DATE >= ?
    and IMPORTACE = ?
    order by TS_DATE asc, TS_TIME asc
  EndOfSQL

  $sth->execute($date, $import);

  my @rows;

  while (my(@row) = $sth->fetchrow_array()) {

    push @rows, [@row];

    if (@rows == 11) {
      $sth->finish();
      last;
    }

  }

  return @rows;

}

}

The most important thing here is adding rows to the results list one at a time, and stopping if you get to 11.

[one4k4]

set rowcount 11

select
  first_name
from
  database
where
  start_date <= '2001/04/14'
  and stop_date > '2999/12/31'


Something similar to that concept in your sql may help.... rowcount works with Sybase. MySQL, I dont know.

Update:I took the talk about his posted code out, and realized it just didnt fit in this thread. Regardless, the rowcount idea, kind of follows the suggesstion(s) above.

_14k4 - [e-mail] (www.poorheart.com)