How Can I Efficiently Select the Latest Items per Category in SQL?

Optimize SQL queries to efficiently select the latest items in each category

The challenge of displaying the latest projects in each category in the database requires an efficient SQL query. While iterating category by category and querying for the latest items is a straightforward approach, it's better to optimize the query by reducing database calls.

Max-N-questions per group

The task of identifying the latest n records in a category group is known as the max-n-per-group problem, which is a common SQL query problem.

External connection solution

Using outer joins we can get the desired result:

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4;

Description

This query performs an outer join between each item (i1) and the item newer than it (i2), considering only items in the same category. The COUNT(*) clause determines the number of newer items for each i1. Projects with fewer than four newer projects become qualified candidates for our selection.

Advantages

This solution is flexible and adaptable:

• Easily handle any number of categories
• Will not be affected by category modification
• Can handle categories with different number of items

Alternatives

Another MySQL-specific solution using user variables:

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 4;

SQL window functions

MySQL 8.0.3 introduced window functions, allowing another efficient solution:

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;

Conclusion

By choosing the appropriate query method based on your specific MySQL version and database structure, you can effectively optimize the selection of the latest items in each category.

The above is the detailed content of How Can I Efficiently Select the Latest Items per Category in SQL?. For more information, please follow other related articles on the PHP Chinese website!