Jumping To a record in SQL

Hi,

I am developing a web application using the oraPerl library to access an ORACLE 7 database. As part of the application we need to be able to jump to a particular record. We cannot use the ROWNUM pseudo column in the WHERE clause as this is calculated before an order by is issued. I have come up with the following solution and was wondering if anyone can think of a better solution (without using PL*SQL) to the solution in normal SQL.

Assume we have a table BUYERS with three fields CD_BUYER, NA_BUYER and we want to look at record 10. The solution I came up with is.

SELECT
  CD_BUYER,
  NA_BUYER
FROM
  BUYERS B1
WHERE 1 <=
  (SELECT
    COUNT(*)
  FROM
    BUYERS B2
  WHERE
    B2.CD_BUYER <= B1.CD_BUYER
  )

ORDER BY           // The order by clause enables you to look at all
  CD_BUYER         // records after record 10

Now this starts to get really complex if you have more than one key or want to do the comparison on a non unique field. To overcome this I have a perl routine to generate the SQL statement. I will not put it here as it is quite complexish.

This method is ok for small amounts of data but for larger volumes (1000+) it starts to slow down a bit, and we have one table with 30,000 records.

Any solutions would be useful, if you want more details of mine let me know.

regards,
paul Received on Tue Oct 29 1996 - 00:00:00 CST