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 1 M_Contacts.Contact_ID,
M_Contacts.Contact_Name, M_Contacts.Contact_Address, M_Contacts.Contact_City,
M_Contacts.Contact_State, M_Contacts. |
| 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 |
