Using user defined var to store max(date) and then use it in having clause

Question

I need to get only the latedt price change of specific products.

• I returned all the records of price changes for the specific products grouped by product name
• I save the max date of price change in a user defined var
• I compare the field that stores the date of price chane with the var in a having clause to get the latest price change

But I just get 0 rows returned

         SELECT  
            pr.product_id
            ,pr.price set_price
            ,pr.start_date -- the timestamp of price change
            ,date(max(pr.start_date))
            ,@'max_change' := max(pr.start_date)

        FROM prices pr

        where product_id in ( -- get the id's of specific products
                            ....                
            )

        group by pr.product_id

        having date(pr.start_date) = date(@max_change)

Answer 1

You could use a correlated sub query for this, for example

drop table if exists member_token;
create Table member_token
(memberProfileId  int,createdAt date);

insert into member_token values
(1,'2019-01-01'),(1,'2019-01-01'),
(2,'2019-01-01'),(3,'2019-01-01'),
(1,'2019-01-02'),(2,'2019-01-02'),
(1,'2019-01-04'),(3,'2019-01-04');


select mt.*
from member_token mt
where createdat = 
(
select max(createdat)
from member_token mt1
where mt.memberprofileid = mt1.memberprofileid) 
order by memberprofileid
;

+-----------------+------------+
| memberProfileId | createdAt  |
+-----------------+------------+
|               1 | 2019-01-04 |
|               2 | 2019-01-02 |
|               3 | 2019-01-04 |
+-----------------+------------+
3 rows in set (0.00 sec)