Top Query Revealed: Simple Top Query

Top Query Revealed: Simple Top Query

Introduction

SQL, the query language of Microsoft Access, has many powerful, yet little known features. One of these is the Top Query. A Top query allows you to limit the results of a query to a certain number (or percentage) of records.

The term "top" is deceiving, because it implies "largest" values. However, this is not strictly the case. You can choose the top largest values or the top smallest values. As I'll show later, you can even choose random records.

Simple Top Query

Suppose I want to create a query which shows me the top 10 invoices (based on price) in my Invoices table.

To create a TOP query in SQL, add the TOP predicate immediately following the SELECT keyword followed by the number of records you want to see:

SELECT TOP 10 * FROM Invoices

But that's not the end of it. To define what is "top", you must sort on one or more fields, using use DESC for largest numbers and ASC for smallest numbers. (Technically, you don't need to add ASC if you wanted the smallest numbers because ascending order is default.) Adding that, the whole query looks like this:

SELECT TOP 10 *
FROM Invoices
ORDER BY [Total Price] DESC;

This will return 10 records with the largest values in the Total Price field. But you can also choose to see the top (or bottom) percent. So if I wanted to see the top 5% best sellers, I could do this:

SELECT TOP 5 PERCENT *
FROM Invoices
ORDER BY [Total Price] DESC;

Although I used the asterisk (*) in the above queries, I did so only for simplicity. Like any query, you can also include a field list in your query, like so:

SELECT TOP 10 Account, OrderNum,
[Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY [Total Price] DESC;

You are not limited to the SQL view of a query to create a Top query, however. To create a TOP query in the Query Builder, open the Query Properties and look for the TOP property. See Figure 1.

Figure1: Shows the Query Builder window to create a Top query.

The drop down box gives you suggestions, but you are not limited to the values in the list. Choosing All, removes the Top predicate from the SQL statement and returns the query to a normal Select query.

The results of this query, whether created in the Query Builder or directly in SQL can be seen in Figure 2.

Figure2: Results of simple Top query.

Simple Top Query With Where Clause

I'm not limited to showing the top values for all invoices. I can also narrow the scope of the query with a Where clause just as you can with any Select query. For instance, if I wanted to see the top 3 invoices of a single Account (say, 237001), my query would look like this.

SELECT TOP 3 Account, OrderNum, [Total Price]
FROM Invoices
WHERE Account=237001
ORDER BY [Total Price] DESC;

Result of top 3 invoices for Account 237001.

As useful as this is, what if I wanted to find the top 10 Accounts in terms of total sales rather than individual invoices? Or suppose instead of the top 10 values for a particular Account, I wanted the top 10 values of each Account. I'll talk about that next time in: Top Query Revealed: Aggregate Values and Top Values By Group.

.