Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual FoxPro
Expertise: Beginner
Nov 5, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Limiting Number of Rows in a SQL SELECT Statement

In Oracle, using where rownum <=100 allows you to limit your rows. How can I accomplish this in Visual FoxPro 6?

I do not know Oracle, so I am not totally sure what this WHERE clause accomplishes, but I believe that the equivalent in VFP is the TOP clause of the SELECT statement. The TOP clause requires an ORDER BY clause.

The following is from the VFP 6 help file regarding the SELECT SQL command:

    Specifies that the query result contains a specific number of rows or a percentage of rows in the query result. You must include an ORDER BY clause when you include the TOP clause. The ORDER BY clause specifies the columns on which the TOP clause determines the number of rows to include in the query result.

    You can specify from 1 to 32,767 rows. Rows with identical values for the columns specified in the ORDER BY clause are included in the query result. Therefore, if you specify 10 for nExpr, the query result can contain more than 10 rows if there are more than 10 rows with identical values for the columns specified in the ORDER BY clause.

    If the PERCENT keyword is included, the number of rows returned in the result is rounded up to the next highest integer. Permissible values for nExpr when the PERCENT keyword is included are 0.01 to 99.99.

Here is an example of querying a customer table while limiting the result set to the first 10 records (in order of Sales) that met the WHERE condition:
   FROM Customers ;
   WHERE Sales>200000 ;
   TOP 10 ;
   ORDER BY Sales ;
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date