How to Select the Top 3 Rows from Each Category in MySQL Without Analytic Functions?

Selecting the Top 3 Rows from Multiple Categories in MySQL

Selecting the top rows from each category within a table can be challenging. One method utilizing views and subqueries, as outlined in the initial attempt, may return incorrect results.

A more effective approach employs analytic functions, which MySQL does not natively support. However, it's possible to emulate these functions using variables. Here's how:

<code class="sql">SELECT x.*
FROM (
  SELECT t.*,
         CASE
           WHEN @category != t.category THEN @rownum := 1
           ELSE @rownum := @rownum + 1
         END AS rank,
         @category := t.category AS var_category
  FROM TBL_ARTIKUJT t
  JOIN (SELECT @rownum := NULL, @category := '') r
  ORDER BY t.category
) x
WHERE x.rank <= 3;</code>

This query initializes variables @rownum and @category using a JOIN operation. It then assigns a rank to each row based on its category, incrementing the rank if the category changes.

Finally, the query selects only the rows with a rank of 3 or less. Note that you may need to modify the SELECT x.* clause to include only the desired columns.

The above is the detailed content of How to Select the Top 3 Rows from Each Category in MySQL Without Analytic Functions?. For more information, please follow other related articles on the PHP Chinese website!