How to Retrieve the Top N Records for Each Group in a Database?

After database grouping, get the previous n records of each group

Your database data is organized by grouping, and you need to extract the first N records of each group. For this reason, you can use the following strategies:

Union all method:

Use the union all computing symbol to combine the first N records of each group. For example, if you have two groups and want to retrieve the first two records for each group, you can use:

Ring number method:

<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>

The following queries are used for each record to generate line number:

This query allocates a line number for each record in its group. Then, you can filter the result with only retrieval of the line number less than or equal to n:

<code class="language-sql">SELECT person, `group`, age,
      (@num:=IF(@group = `group`, @num +1, IF(@group := `group`, 1, 1))) row_number
FROM test t
CROSS JOIN (SELECT @num:=0, @group:=NULL) c
ORDER BY `Group`, Age DESC, person</code>

Other methods:

<code class="language-sql">SELECT person, `group`, age
FROM
(
   ... (上述查询) ...
) AS x
WHERE x.row_number <= N</code>

The XAPRB article mentioned in the article provides other methods to achieve this result, including:

Use sub -query or related child query

Use the storage procedure

Use the window function (for the database supported)
• The best way depends on your specific database system and performance requirements.

The above is the detailed content of How to Retrieve the Top N Records for Each Group in a Database?. For more information, please follow other related articles on the PHP Chinese website!