Get Top 3 from Each MySQL Group

Key words: MySQL, Group, intra-group, top N

 

Below is the MySQL way of doing this. It takes a roundabout route because the database doesn’t support order-based calculations directly.
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

 

Yet it’s a piece of cake to get this done in SPL (Structured Process Language). Only a one-liner is enough:

A.group(g;~.top(-3;x)).conj(#2)

 

* To get rows where a specified field value is greater than the median from each group;

* To get the maximum growth rate in each group;

* To get groups where values of a specified field rises consecutively;

SPL is good at handling these types of calculations over subsets or detailed data in a group. More explanations and examples can be found in Getting Top N Problem Optimization in SPL, Intra-group Calculation Optimization in SPL and Inter-row Calculation Optimization in SPL.

 

esProc is a desktop programming tool. It’s easy to install, and it’s convenient to debug code and view data in it:

Download esProc

esProc is powerfully independent yet integration-friendly. You can easily embed an SPL script into any Java program.

How to Call an SPL Script in Java