How Can I Retrieve the Newest Items from Multiple Categories Using a Single SQL Query?

Single SQL Query for Retrieving the Most Recent Items from Various Categories

This article presents efficient SQL solutions for retrieving the most recent items across multiple categories using a single database query, minimizing database calls.

SQL Solution: Leveraging Outer Joins

The "greatest-n-per-group" problem is elegantly solved using outer joins:

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(i2.item_id) < 4;

This query efficiently selects items with fewer than four newer items within their respective categories.

Explanation:

The query joins each item (i1) with other items (i2) in the same category that have a higher item_id (meaning they are newer). The HAVING clause filters out items with three or more newer items, leaving only the four most recent items per category.

Advantages:

• Scalability: Handles any number of categories effectively.
• Robustness: Works correctly even with varying numbers of items per category or changes to category structure.

Alternative Approaches:

• MySQL User Variables:

This method employs MySQL's user-defined variables to assign row numbers and identify the four newest items per category. (This approach is less portable than the outer join method).

• Window Functions (MySQL 8.0.3 and later):

MySQL 8.0.3 supports standard SQL window functions, providing a more concise solution:

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

This approach partitions items by category, assigns row numbers based on item_id (in descending order for newest first), and then selects the top four rows from each partition.

The above is the detailed content of How Can I Retrieve the Newest Items from Multiple Categories Using a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!