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 |
CodePudding user response：
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.
CodePudding user response：
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.
CodePudding user response：
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