In developing database applications, usually it is the records corresponding to the max/min value that we need to retrieve, instead of the value itself. For example, the occasion in which each employee gets his/her biggest pay raise; the three lowest scores ever got in golf; the five days in each month when each product gets its highest sales amount; and so on. As the max function of SQL can only retrieve the max value, instead of the records to which the max value corresponds, it is quite complicated to handle the computation in SQL with the aid of some advanced techniques, such as window functions or the nested sub-queries or keep/top/row number. If multi-layered grouping or relations are involved, the computation will become even more complicated.
With the top function in esProc, the records corresponding to the max value can be retrieved and the computation becomes much easier. The following is such an example.
The database table golf contains the scores of members in a golf club. Please select the best three scores each member has ever got. Part of the data is as follows:
In the above, the computation is performed step by step. But the steps can be integrated into one for the convenience of maintenance and debugging: db.query("select * from golf").group(User_ID). (~.top(-Score;3)).union().
The method with which a Java program calls esProc is similar to that with which it calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents