Introduction to the TOP
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.
Illustration 1: Using TOP from the Top Values Combo Box in
the Design View
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
SELECT TOP 10 ProductName, UnitPrice
ORDER BY UnitPrice DESC
example query delivers the dataset shown in Illustration 2.
Illustration 2: Example Query Results - TOP Keyword
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.
inject the PERCENT keyword into our example, to explore the syntax
surrounding its use.
SELECT TOP 10 PERCENT ProductName, UnitPrice
ORDER BY UnitPrice DESC
modified query delivers the dataset shown in Illustration 3.
Illustration 3: Example Query Results - TOP and PERCENT
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.