如何在GROUP BY分组时获取最新记录、产品统计与最新产品标题？

Hey there! Let's work through your SQL grouping problem step by step.

First, let's figure out why your original query is returning null for the title:

• Your CASE statement is missing the required END clause (that's a syntax error waiting to happen!)
• More importantly, after grouping by catID, the p.id in your condition isn't a single row's ID—it refers to all IDs in the group. Comparing that to MAX(p.id) will never evaluate to true, so the CASE returns null by default.

Now, let's tackle your requirement: get the total number of products in a category and the title of the most recently added one, preferably without subqueries. Here are a couple of solid solutions based on your database setup:

Solution 1: Window Functions (Modern Databases: MySQL 8+, PostgreSQL, SQL Server, etc.) #

This approach is clean, reliable, and avoids subqueries entirely. We use window functions to calculate the count and grab the latest title in one pass:

SELECT DISTINCT
  catID,
  COUNT(*) OVER (PARTITION BY catID) AS items,
  FIRST_VALUE(title) OVER (PARTITION BY catID ORDER BY timestamp DESC) AS latest_title
FROM products
WHERE catID = 1;

• COUNT(*) OVER (PARTITION BY catID) calculates the total number of products in the category for every row.
• FIRST_VALUE(title) OVER (...) grabs the title of the row with the newest timestamp in the category.
• DISTINCT ensures we only get one row per category instead of repeating the same values for every product.

Solution 2: GROUP_CONCAT Trick (MySQL 5.x and Below) #

If you're stuck on an older MySQL version that doesn't support window functions, this method works without subqueries (just watch out for special characters in titles):

SELECT 
  catID,
  COUNT(id) AS items,
  SUBSTRING_INDEX(GROUP_CONCAT(title ORDER BY timestamp DESC SEPARATOR '|'), '|', 1) AS latest_title
FROM products
WHERE catID = 1
GROUP BY catID;

• GROUP_CONCAT(title ORDER BY timestamp DESC) concatenates all titles in the category, sorted from newest to oldest (we use | as a separator to avoid issues if titles have commas).
• SUBSTRING_INDEX(..., '|', 1) extracts the first title from the concatenated string, which is the latest one.

Verification with Your Sample Data #

Both queries will return:

That's exactly the result you're looking for!

内容的提问来源于stack exchange，提问作者John Sly