devxlogo

Limiting Number of Rows in a SQL SELECT Statement

Limiting Number of Rows in a SQL SELECT Statement

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

Answer:
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:

 TOP nExpr [PERCENT]

    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:

 SELECT Name ;   FROM Customers ;   WHERE Sales>200000 ;   TOP 10 ;   ORDER BY Sales ;   INTO CURSOR XYZ

See also  Why ChatGPT Is So Important Today
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist