Get multiple records with the maximum value in one line


It is not difficult to get the first piece of data through the top clause in SQL, and then with sorting, you can get the row with the maximum value:

select top 1 * from T order by f desc

If the maximum value has multiple records, and you want to get all of them, you need to filter the data again by the subquery criteria to get all the records with the maximum value.


 It would be much easier with esProc SPL language in this case. Get raw data from database:

>T=connect(”mysqlDB”).query(“select * from T”)

Then use the @a option of maxp to get all the maximum records:


The above usage is a small improvement. There are more awkward and convoluted calculations in SQL. The reason lies in that the relational algebra system relied on by SQL does not support ordered set calculation, cannot use the position of data in the set, and does not balance the relationship between set and discrete data. SPL changes the topN in SQL when calculating the ordered set. It can take the value / record / record position in the set of the topN, so as to meet the broader calculation demand. TopN in SPL can be applied to the grouped subsets to enhance the ability of subsequent operation of grouping. Refer to TopN and variants.

When the data is not in the database, it is still convenient for SPL to perform complex calculations:


It's also easy to embed esProc into Java applicationsplease refer to How to Call an SPL Script in Java

For specific usage, please refer to  Getting started with esProc