Take the top three of each group from MySQL in one line

 

MySQL doesn't directly support order operation. It's a bit cumbersome to write these, like the following:


select t.*
from
    (select t1.*,
            (select count(*)+1
                from A
                where g=t1.g
                   and x>t1.x
            ) as group_id
        from A t1
    ) t
where group_id<=3
order by g asc, group_id asc

 

In this scenario, it would be easier to use esProc SPL, with only one line:

=connect(”mysqlDB”).query(“select * from A”).group(g;~.top(-3;x)).conj(#2)

*How to retrieve rows larger than the median in a group?

*What is the largest increase in the group?           

*Which groups have experienced continuous rise?

SPL is good at solving such grouped subsets and ordered calculation within groups, such as  《SPL Simplified SQL Case Details: Calculate the first N rows of each group》  ; please refer to《SPL Simplified SQL Case Details: Intra-group computing》 and 《SPL Simplified SQL Case Details: Interline Computing

 

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

=file(“d:/t.csv”).import(;,",").group...

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