- SQL TOP 10 query in the query editor
- SQL query with "TOP n" through programming
How to create an SQL query to get a TOP 10?
To perform a TOP 10
query with the best 10 records from a data file, you can create a loop and set a condition to break out of the loop after 10 iterations.
The best method to perform this TOP 10 query consists in using an SQL query.
Let's see how to perform a TOP 10 query by using:
This method can be applied to a TOP 3 query or to any other TOP query corresponding to a selection of the first or last N results.
SQL TOP 10 query in the query editor
This query is used to perform a TOP 10
, which means the best 10 results from a selection of records coming from a data file.
In this example, we are going to perform the TOP 10 for the best 10 orders.
The steps for creating this SQL query with filter are as follows:
- Click in the quick access buttons.
- In the window that is displayed, click "Queries".
- The query creation wizard starts.
- Specify that you want to create a select query ("Select" option).
Go to the next step of the wizard.
- The query description window appears.
- Give a name and a caption to the query:
- In the left section of the description window, choose the file items that will be used (in our example, the ORDER file and the OrderNum and TotalIOT items).
- Double-click the names of the items to add them to the list of query items:
- A TOP is based on a sort. In the example, we are going to sort the orders on the TotalIOT item in descending order. To apply a sort, click "Sort" in the right section of the editor.
- Select "Sort by the selected item" from the menu that is displayed.
- In the window that appears:
- Select the sort order ("Descending" in our example).
- To create a TOP query, click "The first n..." in the right section of the editor.
- In the window that appears:
- Select the option "Select the first records (TOP)".
- Specify the number of records to select (in our example, 10 for a TOP 10).
- Validate the window.
- Validate the query description window. The query is displayed in the editor:
- Save the query (Ctrl + S).
- Press F2 to see the SQL code:
- Run the query test (GO in the quick access buttons).
- The query can be run in the program by HExecuteQuery.
SQL query with "TOP n" through programming
The SQL queries can be directly written through programming in the WLanguage code. To do so, you must:
- Create a variable of type Data source to represent the query at runtime.
- Create a Character String variable to contain the SQL code of the query and write the SQL code in this variable.
- Run the SQL query with HExecuteSQLQuery.
- Browse the result with the HReadXXX functions.
Src1 is Data Source
sSQLCode is string
// Select the best 5 customers according to the sum of orders ...
sSQLCode = [
SELECT TOP 5
CUSTOMER.CustomerNum AS CustomerNum,
CUSTOMER.CustomerName AS CustomerName,
CUSTOMER.City AS City,
SUM(ORDERS.TotalIOT) AS the_sum_TotalIOT
CUSTOMER.CustomerNum = ORDERS.CustomerNum
HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.CustomerNum, Src1.CustomerName, Src1.City)
This page is also available for…