PC SOFT

ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
  • Overview
  • SQL TOP 10 query in the query editor
  • SQL query with "TOP n" through programming
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
How to create an SQL query to get a TOP 10?
Overview
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:
  1. Click New in the quick access buttons.
    • In the window that is displayed, click "Queries".
    • The query creation wizard starts.
  2. Specify that you want to create a select query ("Select" option).
    Query creation wizard

    Go to the next step of the wizard.
  3. The query description window appears.
  4. Give a name and a caption to the query:
    Name and caption of the query
  5. 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).
  6. Double-click the names of the items to add them to the list of query items:
    Selection of items
  7. 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.
    Sort
  8. Select "Sort by the selected item" from the menu that is displayed.
  9. In the window that appears:
    • Select the sort order ("Descending" in our example).
      Choosing the sort order
    • Validate.
  10. To create a TOP query, click "The first n..." in the right section of the editor.
    First N
  11. 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).
      Configuration of TOP
  12. Validate the window.
  13. Validate the query description window. The query is displayed in the editor:
    Displaying the query in the editor
  14. Save the query (Ctrl + S).
  15. Press F2 to see the SQL code:
    SQL code of query
  16. Run the query test (GO in the quick access buttons).
  17. 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:
  1. Create a variable of type Data source to represent the query at runtime.
  2. Create a Character String variable to contain the SQL code of the query and write the SQL code in this variable.
  3. Run the SQL query with HExecuteSQLQuery.
  4. Browse the result with the HReadXXX functions.
Code example
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
FROM
CUSTOMER,
ORDERS
where
CUSTOMER.CustomerNum = ORDERS.CustomerNum
 
GROUP by
CUSTOMER.CustomerNum,
CUSTOMER.CustomerName,
CUSTOMER.City
ORDER by
the_sum_TotalIOT DESC
]
 
HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.CustomerNum, Src1.CustomerName, Src1.City)
END
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment