Our
next step will be to limit the transactions to the year 1997. Our
results dataset currently displays the combined Order information for
all years in the table. We can restrict the data to the year under examination
with the addition of a simple WHERE clause, as we shall see.
23.
Shift back to SQL
view, once again.
24.
Add the following
WHERE clause between the existing FROM and GROUP BY
clauses:
WHERE ShippedDate Between #1/1/1997#
And #12/31/1997#
This
will restrict the records returned to those representing transactions with Shipped
Dates that lie between January 1st and December 31st,
inclusive.
25.
Save the query
as ACC08-03.
The
query should now resemble that shown in Illustration 10.
26.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
11.
Illustration 11: The Dataset Retrieved by the Modified
SELECT Query (Partial View)
We can
see that the transactions now appear to be limited to those that occurred in
1997, a circumstance that is also confirmed by the fact that the total number
of records has again decreased markedly, this time to just under 400.
We are
now ready to insert the TOP keyword to drive the generation of the "top"
twenty records. Recall that the ORDER BY clause actually makes this
work. We are simply going to order our existing result dataset by Shipped
Date, descending, so that we obtain the records by date, most recent to
oldest. Then, when we insert the TOP keyword, we are essentially asking
for the "top twenty" of that list (which is ordered, again, most
recent to oldest). We are thus saying "give me the twenty most recent"
- or the twenty last - transactions that occurred in 1997.
27.
Shift back to SQL
view, once again.
28.
Add the
following to the SELECT clause, to the immediate right of the word SELECT:
TOP 20
29.
Add the
following ORDER BY clause to the end of the query (be sure to remove the
semicolon (";") that ends the line above, if it is present).
ORDER BY
Orders.ShippedDate DESC;
30.
Save the query
as ACC08-04.
The
query should now resemble that shown in Illustration 12.
Illustration 12: The SELECT Query with the TOP Keyword Modifications
31.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
13.
Illustration 13: The Dataset Retrieved by the Modified
SELECT Query (Partial View)
We
immediately see the effects of the TOP keyword, together with the ORDER
BY clause through which it is driven. We see, as well, how we can use TOP
with dates, as well as the more intuitive number data types, to deliver a
results dataset from a range of dated transactions. An examination of the
number of records in the dataset, however, presents a perplexing phenomenon:
the counter indicates "22," instead of the twenty records we
might have expected.
The
reason for the apparent deviation from the expected results is simply a
manifestation of the behavior of the TOP keyword in the event of a "tie."
When equal values are present in the range from which we are selecting the "top"
records, all records that have the equal value are returned.
To
prove this concept, take the following step.
32.
Select SQL
View using the View Selector button once again.
The Query
appears.
33.
Change the 20
after TOP to 19.
34.
Save the query
as ACC08-05.
35.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
14, and is limited to nineteen rows, because the nineteenth Shipped Date
from the end of 1997 has only one occurrence, 16-Dec-1997.
Illustration 14: Results of Modification of the TOP
Keyword
» 
Comment and Contribute
