How to Efficiently Select Top Rows per Category in MySQL Without Analytic Functions?

Selecting Top Rows per Category in MySQL

To retrieve a limited number of rows from each category in a table, you can utilize analytic functions. However, MySQL doesn't offer these functions directly. Nevertheless, it's possible to emulate them using variables.

Emulating Analytic Functions

The following MySQL query emulates the functionality of analytic functions to select the top 3 rows for each category:

<code class="mysql">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 <p><strong>Explanation</strong></p>
<ul>
<li>The subquery selects all rows from the TBL_ARTIKUJT table and initializes two session variables, @rownum and @category, to track the current ranking and category.</li>
<li>rank assigned to each row indicates its position within its category. When a new category is encountered, rank is reset to 1.</li>
<li>var_category is used to store the category for each row.</li>
<li>The outer SELECT statement filters the subquery, selecting only rows with a rank less than or equal to 3.</li>
</ul>
<p>This method allows you to implement the desired functionality without relying on analytic functions, which are not supported by MySQL.</p></code>

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