Question 21954 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL query | Select latest row from groups

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
Tags:

3

Answers


  1. 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.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search