How to Get the Top 3 Rows From Each Category in MySQL Without Analytic Functions?

Selecting the Top 3 Rows from Each Category in MySql

In a table with numerous records and a category column, the task arises to retrieve only the top two articles from each category. To achieve this, the user initially attempts to create a view limitrows, limiting the number of rows retrieved to two per category. However, this approach proves insufficient.

Understanding Analytic Functions

The desired result requires the use of analytic/windowing/ranking functions. These functions, which MySQL does not inherently support, can be approximated using variables.

Emulating Analytic Functions

The following code emulates analytic functions:

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

The variables @rownum and @category are used to keep track of row number and the current category, respectively. By using these variables, the rank of each row within its category is determined and those within the top 3 ranks are selected.

Customizing the Result Set

To ensure that only the desired columns are returned, specify the required columns in the SELECT statement, as demonstrated below:

SELECT x.articleid, x.title
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

The above is the detailed content of How to Get 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!