mysql 分组取第一行

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(255),
    created_at TIMESTAMP
);

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC) as rn
    FROM products
) t
WHERE t.rn = 1;

SELECT p1.*
FROM products p1
JOIN (
    SELECT category_id, MIN(created_at) as min_created_at
    FROM products
    GROUP BY category_id
) p2 ON p1.category_id = p2.category_id AND p1.created_at = p2.min_created_at;

SET @row_number = 0;
SET @category_id = NULL;

SELECT *
FROM (
    SELECT *,
           @row_number := IF(@category_id = category_id, @row_number + 1, 1) as row_number,
           @category_id := category_id
    FROM products
    ORDER BY category_id, created_at DESC
) t
WHERE t.row_number = 1;