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

MySQL Query: Selecting the Top 3 Rows from Each Category in a Table

In this scenario, you have a table with numerous records, categorized by category. Your goal is to retrieve only the top three articles from each category. While you attempted a solution using views and LIMIT, it resulted in a limited number of records.

To address this challenge, it's necessary to utilize analytic functions, which MySQL lacks. However, you can achieve a similar effect using variables:

<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 employs variables @rownum and @category to track row ranking within categories. It starts by setting both variables to NULL/empty values.

For each row in the table (aliased as "t"), it checks if the current category differs from the previous one. If it does, it assigns 1 to @rownum; otherwise, it increments @rownum by 1. Simultaneously, it assigns the current category to @category to maintain the context for comparison in subsequent rows.

The result is a temporary table that ranks each row within its category. The final WHERE clause then filters this temporary table to display only rows with a rank of 3 or lower.

Remember to adjust the column references in the outer SELECT clause (x.*) to match the columns you want to retrieve.

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