Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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
DevX Pro
 
Comment and Contribute

 

 

 

 

 


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

 

 

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