visual fox pro recno() function

Question

The recno() function work only with a single table. When there are joins it doesn't work. Since my data are huge I want to retrieve few rows at a time using row number. Is there other way to do this without using store procedure but oledb.

Answer 1

You can use nested queries.

Example:

SELECT t0.Customerid, t0.Orderid ;
  FROM ( ;
    SELECT t1.Customerid, t1.Orderid, RecNo() AS rownum ;
      FROM ( ;
        SELECT t2.Customerid, t3.Orderid ;
          FROM Customers AS t2 ;
          INNER JOIN Orders AS t3 ON (t2.Customerid = t3.Customerid) ;
          ORDER BY t2.Customerid
        ) AS t1 ;
      ) AS t0 ;
  WHERE t0.rownum BETWEEN (1) AND (10)