Is there a way to write a query that just gives you the top 10 (or 20 or 101) rows? I'll cover Access first, then FoxPro. In Access, this query, which orders candidates according to their fundraising, doesn't limit the result to any rank: SELECT DISTINCTROW CandName, Receipts FROM CandRept ORDER BY Receipts DESC. Adding one keyword and a number gives the same results, but with a specified cutoff in rank: SELECT TOP 10 CandName, Receipts FROM CandRept ORDER BY Receipts DESC.
There is no opposite keyword for TOP to get the bottom of the list. Instead, you do that by specifying whether the ranked columns should be ordered ascending or descending. You can also add the keyword PERCENT to make the query return only a certain portion of the ranked data: SELECT TOP 10 PERCENT CandName, Receipts FROM CandRept ORDER BY Receipts DESC. In FoxPro, this SQL will return the top 10 fund-raisers: SELECT CandName, Receipts FROM CandRept ; WHERE RECNO () < 11 ; ORDER BY Receipts DESC.
The trick here is in the WHERE clause and the built-in function, RECNO(). For every table and query result, FoxPro can figure out the total record count and the record number of every row. Changing the expression in the WHERE clause lets you limit the results to a certain percent of the full set of rows: SELECT CandName, Receipts ; FROM CandRept ; WHERE RECNO () < 20 * (RECCOUNT () / 100) ; ORDER BY Receipts DESC.
The built-in function RECCOUNT() determines how many rows are in the complete query result.