PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • SQL TOP 10 query in the query editor
  • SQL query with "TOP n" by programming
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
How to create an SQL query to get a TOP 10?
Overview
To perform a TOP 10 records coming from a data file (or the best 10 records from a selection), you have the ability to perform a browse loop and to use a condition to exit from the loop after the 10th passing.
The best method to perform this TOP 10 consists in using an SQL query.
Let's see how to perform a TOP 10 by using:
This method can be applied to a TOP 3 or to any other TOP corresponding to a selection of 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. Select "File .. New .. Query". The wizard for query creation starts.Click New among the quick access buttons.
    • Click "Queries" in the wheel that is displayed. In the window that is displayed, click "Queries".
    • The wizard for query creation starts.
  2. Select the "Select" option and validate.Specify that you want to create a select query ("Select" option).
    Wizard for query creation

    Go to the next wizard step.
  3. The description window of query is displayed.
  4. Give a name and a caption to the query:
    Name and caption of query
  5. In the left section of 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 items to add them into 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 perform this sort, click the "Sort" button in the right section of the editor.
    Sort
  8. Select "Sort on the selected item" from the menu that is displayed.
  9. In the window that is displayed:
    • Select the sort order ("Descending" in our example).
      Choosing the sort order
    • Validate.
  10. To perform this sort, click the "The first n..." button in the right section of the editor.
    First N
  11. In the window that is displayed:
    • 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 description window of query. The query is displayed in the editor:
    Displaying the query in the editor
  14. Save the query (CTRL S).
  15. Press the F2 key 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" by programming
The SQL queries can be directly written by programming in the WLanguage code. To do so, you must:
  1. Create a Data Source variable to represent the query at run time.
  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 sample
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 required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment