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