limiting in Sybase--help please

Anon

In trying to convert my mySQL database over to a Sybase database, the only real problem I've run into is that Sybase doesn't seem to have a LIMIT option for the SELECT statement. Is there any other way to do this?? Perhaps a subselect?? I'd hate to have the select statement return everything and then have PHP choose the range of output. Any help would be appreciated, greatly!

ingooellers

Use SELECT TOP n ... FROM ...

For slices a la LIMIT N,Range you have to write a cursor which fetches the desired rows. Not quite easy

Regards
Ingo

kparker

Ouch. If it's any comfort, you may be able to get close to what you need if there's a unique key for the ordering:

$limit = 0;
$range = 10;
for (;😉
  {
  $q = "select top $range * from table where key > $limit";
  if (! ($r = sybase_query( $q, $link )))
    die("query error: $php_errmsg");
  elseif (sybase_num_rows($r) < 1 )
    break; // no more data == on even boundary
  else
    {
    for ($n = 0; $n < $range && $data = sybase_fetch_object($r); $n++)
      {
      // do something with the data
      }
    if (! $data) // ran out of data before $range
      break;
    $limit = $data->range; // set new limit
    }
  }

kparker

That line:

  $limit = $data->range; // set new limit

should of course really be:

    $limit = $data->key; // set new limit

I.e. we are using the field 'key' from the last record to set the new (exclusive) lower limit for the next query. Also of course, if 'key' is not unique, this method will not work because if you say > you will omit and 2nd and subsequent records with the same key value, and if you say >= you may get one or more of them repeated at the start of the next query.