How to Efficiently Extract the Top N Records from Grouped Data?

The top records in the high -efficiency extraction of group data

Assume that there is a structured data set, including personnel, grouping, and age. This article discusses how to obtain the former N individual in each group according to age.

Expected results:

The goal is to retrieve the two oldest individuals in each group.

Existing solution:

The previous method (inspired by the query proposed by @Bohemian) to retrieve a single top line for each group:

Enhanced solution using Union:

<code class="language-sql">select * 
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`</code>

One way to extend this function is to use the Union All operator, so that the record of the specified quantity for each group can be retrieved:

Use the alternative of the line number:

Another method is to generate a line number for each record, and then screen according to this line number:

<code class="language-sql">(
  select *
  from mytable 
  where `group` = 1
  order by age desc
  LIMIT 2
)
UNION ALL
(
  select *
  from mytable 
  where `group` = 2
  order by age desc
  LIMIT 2
)</code>

Conclusion:

Based on specific requirements and data features, both UNION and line number technology provide effective solutions to retrieve top records in the group results.

The above is the detailed content of How to Efficiently Extract the Top N Records from Grouped Data?. For more information, please follow other related articles on the PHP Chinese website!