How to get most recent record with a group by

user_1855372 0
Viewed 67

How can I get the latest product title when I'm doing a group by category?

For example, assume this is my table of products:

id | title | catID | timestamp
1  | apple | 1     | 2020-07-13 08:21:47
2  | pear  | 1     | 2020-07-14 08:21:47
3  | kiwi  | 1     | 2020-07-15 08:21:47

I want a query to give me the total products and the last added. It's a little greedy of a query, and makes it a little more difficult, but I thought I could do this with a group by. If possible, I'd really prefer to keep this all one query. I'm worried I'll need to add a sub query though.

SELECT
    p.catID
    , COUNT(p.id) as items
    , MAX(p.id) as last_added_id
    , CASE WHEN p.id = MAX(p.id) THEN p.title
FROM products p
    WHERE p.catID = 1
GROUP BY p.catID

The problem is, I'm getting a null for the title.

mysql
3 Answers

I actually can't think of a way to do this without using a subquery. Assuming you are using MySQL 8+, we can use window functions here:

SELECT id, title, catID, timestamp, cnt
FROM
(
    SELECT *, COUNT(*) OVER () AS cnt
    FROM products
    WHERE catID = 1
) t
ORDER BY timestamp DESC
LIMIT 1;
user_18632290

With window functions COUNT(), MAX() and FIRST_VALUE():

select distinct
  p.catid,
  count(*) over (partition by p.catid) items,
  first_value(p.id) over (partition by p.catid order by p.timestamp desc) id, 
  first_value(p.title) over (partition by p.catid order by p.timestamp desc) title,
  max(p.timestamp) over (partition by p.catid) timestamp
from products p

See the demo.
Results:

> catid | items | id | title | timestamp          
> ----: | ----: | -: | :---- | :------------------
>     1 |     3 |  3 | kiwi  | 2020-07-15 08:21:47
user_104988280

I dont know if this what you mean.

SELECT
    p.catID
    COUNT(p.id) as items
    MAX(p.id) as last_added_id
    sum( if( p.id = 1, p., 0 ) ) AS Total,
    (SELECT max(title) FROM product WHERE catID= 1) 
FROM products p
    WHERE p.catID = 1
GROUP BY p.catID
user_123963020
Related