MS Access for the Business Environment: Access Query Techniques: Using the TOP Keyword - Page 2
January 5, 2004
Introduction to the TOP Keyword
The need to limit a list to a "top" number or percentage is quite common in business, as it is in many other facets of life. The "top ten" customers with regard to revenue, for example, for a given year would be a useful list for most organizations, in that it would be likely to present a clear idea of which customers have contributed the most to the all-important profit margins. By the same token, a "bottom" number or percentage can prove useful, as well; it can serve, for example, as an indicator of a good place to initiate steps to improve performance, or to take other remedial or preventative action.
We can use the TOP capability in the QBE pane of MS Access by simply typing (or selecting one of the defaults for) the number by which we wish to narrow a query's results dataset into the combo box provided for that purpose, as shown in Illustration 1, should we be using the Design view approach to writing a simple query to avoid writing "direct SQL." As we mentioned in our last lesson, however, the more real life business requirements we encounter, the more likely we will find ourselves in scenarios where we need to understand the SQL that is constructed behind the queries we create within the graphical environments. This lesson will focus on the construction of the SQL required to use the TOP keyword, along with the optional PERCENT keyword, to help us to leverage the TOP capabilities to their fullest, and to prepare us for their use in Access Query Techniques: Subqueries, Part II.
As we noted in the introduction, the TOP keyword allows us to restrict the number of rows returned in a results dataset to a number that we specify. The TOP keyword works in conjunction with the ORDER BY clause, and, in fact, is rendered useless without it. For example, if we order our results by a given value, in descending order, and we use TOP to specify twenty values, then it is obvious that the twenty values returned, being the top twenty in the results dataset, are the largest of the entire set.
Should we have specified ascending in the ORDER BY clause, the "top twenty" returned by the same TOP construction would actually be the bottom twenty values of the entire results dataset, as the first twenty entries in the ordered set would begin with the lowest value and continue in ascending order through the twentieth value.
It thus becomes clear why TOP is ineffective without the ORDER BY clause: the absence of order in the returned dataset would mean that the "top twenty" records selected from our dataset would be in no particular sequence, resulting in a haphazard selection of twenty records with no discernable pattern.
Using the PERCENT keyword in conjunction with the TOP keyword allows us to return a percentage of dataset rows, in contrast to the number of rows that we get when we use TOP alone. But like the solitary TOP keyword, the combination of TOP and PERCENT returns its results from the top or the bottom of the dataset, depending upon the selection within the ORDER BY clause.
Let's take a look an example query, where we employ the TOP keyword as a means of examining the syntax involved:
SELECT TOP 10 ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC
The example query delivers the dataset shown in Illustration 2.
In this simple example, we are asking that the top ten records, in terms of Unit Price, be delivered from the population of the Products table; this results because we have asked for the top ten in a list of Products, which is sorted from highest to lowest with respect to Unit Price. The ten highest priced Products, therefore, compose the result dataset.
Now let's inject the PERCENT keyword into our example, to explore the syntax surrounding its use.
SELECT TOP 10 PERCENT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC
The modified query delivers the dataset shown in Illustration 3.
We have modified our query to request the records that compose ten percent of the rows in the Products table. This turns out to be ten percent of 77 total rows (a simple Select * FROM Products shows 77 to be the total number of records in the results dataset), or, as our illustration indicates, eight rows (the closest we can get with whole rows) from our ordered range.
Let's move into a couple of hands-on illustrations to reinforce our understanding of these concepts, using the Northwind sample database.