<%dim crumb crumb2="Select Top Query Tutorial" crumb1="Select Top n Records
Select Top Predicate Example Code" crumb="Select Top 1 Record" %> Select Top Query in Access
Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
<% response.write crumb2 %>
<% response.write crumb1 %>
Home > Tutorials > Query Tutorials > <% response.write crumb %>

Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function


MultiSelect Parameter Forms Access Tutorial

Select Top Query

Select Top n Records Predicate Example

Have you ever had the need to get the 3rd record from a table using a query only? Well, in case you want to know how to do it here is the select top records query solution:

1) Create a query (Query1) to get the top three records.  We are interested in field called Submit_Date from a table called M_Revisions:

Select Top 3 M_Revisions.Submit_Date from M_Revisions
Order by M_Revisions.Submit_Date;

2) Use Query1 as input to a new query:

Select Top 1 Query1.Submit_Date from Query1
Order by Query1.Submit_Date DESC;

Now you have the 3rd submit date.  Note that sorting the Query1 records in descending order makes the 3rd record go to the top.

You can use Select Top query to retrieve any number of records such as:

  • Select Top 10

  • Select Top 100

  • Select Top 123

As shown above you can use the sort option (Group By) to arrange the records in the order you want to select the appropriate top records.  You can combine this with the scalar query option to perform complex operations with your queries.

The select top query is not without its problems.  We have noticed a bug in the select top predicate query when you are selecting from long text fields where the first 20 to 30 characters are the same in the text data.  For instance, if you use Select Top 100 you may get back 110 records.

More select top query examples:

Select Top Query in Repairing Corrupted Table

SQL Select Top with Order By

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365

Contact Information
<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials