MySQL query | Select latest row from groups

Question

I have a table similar to this:

product_id	client_id	category	price	created_date
1	1	A	3.1	2022-11-01
2	1	A	3.2	2022-11-02
3	1	B	3.3	2022-11-03
4	1	B	3.4	2022-11-04
5	2	B	3.5	2022-11-05
6	2	B	3.6	2022-11-06
7	2	A	3.7	2022-11-07
8	2	C	3.8	2022-11-08

And I want to select the price from the latest created_date from each client_id and category, so my expected result would be this:

product_id	client_id	category	price	created_date
2	1	A	3.2	2022-11-02
4	1	B	3.4	2022-11-04
6	2	B	3.6	2022-11-06
7	2	A	3.7	2022-11-07
8	2	C	3.8	2022-11-08

Could you please help me with this? Thanks

I found something similar here: Select first row in each GROUP BY group?

UPDATE

Actually I want to do the same with the following (this is a more realistic example): https://www.db-fiddle.com/f/fHc6MafduyibJdkLHe9cva/0

Expected result:

val1	val2	num1	num2	created_date
X	A	33	333	2022-11-03
X	B	66	666	2022-11-06
X	C	88	888	2022-11-08
X	D	99	999	2022-11-09
Y	A	111	1111	2022-11-11

learning · Answer 1 · 2022-12-02T01:21:11.303

0

You can do in this way. Use MAX to get the latest date first and group by category and client ID.

Check out this db_fiddle

SELECT a.pid, b.cid, b.category, a.price, b.created_date FROM products a
JOIN 
    (SELECT category, cid, MAX(created_date) as created_date FROM products GROUP BY cid, category) as b
    ON a.category = b.category AND a.cid = b.cid AND a.created_date = b.created_date
ORDER BY pid

Please provide DDL + DML commands next time when you ask question so that others don't need to prepare DDL and DML commands by their own. It is advisable to share what you've tried as well.

edited Dec 02 '22 at 01:21

answered Dec 02 '22 at 01:15

learning

608
5
15

Thanks for your support, this is was I tried before asking: SELECT t1.product_id, t1.client_id, t1.category, t1.price, t1.created_date FROM my_table AS t1 WHERE t1.created_date = (SELECT MAX(t2.created_date) FROM my_table AS t2 WHERE t1.product_id = t2.product_id) GROUP BY t1.client_id, t1.category -- https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html – JohnP Dec 02 '22 at 01:18
can you pls update this in your question and share us the result of your query? Did you hit any error running your query? – learning Dec 02 '22 at 01:20
@JohnP I believe you hit the error when you run this query. GROUP BY list should be the same as your selected columns. You selected product_id, client_id, category, price and created_date but you only group client_id and category? Please try out my solution – learning Dec 02 '22 at 01:25

score 0 · Answer 2 · answered Dec 02 '22 at 01:51

0

written in postgresql standard. It may helpful for you.

select 
distinct
first_value(pid) over(w) as pid,
first_value(cid) over(w),
first_value(category) over(w),
first_value(price) over(w),
first_value(created_date) over(w)
from products
window  w as (partition by cid,category order by created_date desc)

Tested in Postgresql DB. it is working as expected.

answered Dec 02 '22 at 01:51

Learn Hadoop

2,760
8
28
60

His rdbms is mysql – learning Dec 02 '22 at 01:58
1

it should work.. sql written in ANSI standard – Learn Hadoop Dec 02 '22 at 02:02

score 0 · Answer 3 · answered Dec 02 '22 at 02:16

If your MySQL version is 8.0 or higher,then we can use windows function to do it

create table test_data(
product_id int,
client_id int,
category varchar(10),   
price float,
created_date date
);

insert into test_data(product_id,client_id,category,price,created_date) values
(1,1,'A',3.1,'2022-11-01'),
(2,1,'A',3.2,'2022-11-02'),
(3,1,'B',3.3,'2022-11-03'),
(4,1,'B',3.4,'2022-11-04'),
(5,2,'B',3.5,'2022-11-05'),
(6,2,'B',3.6,'2022-11-06'),
(7,2,'A',3.7,'2022-11-07'),
(8,2,'C',3.8,'2022-11-08');


SELECT t.*
FROM 
(SELECT *,
 row_number() over(PARTITION BY client_id,category order by created_date DESC)  as tn from test_data) as t
WHERE t.tn=1
order by t.product_id

DB Fiddle Demo

MySQL query | Select latest row from groups

3 Answers3