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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may