How to select the latest price for product?

Question

Here is my table:

+----+------------+-----------+---------------+
| id | product_id |   price   |     date      |
+----+------------+-----------+---------------+
| 1  | 4          | 2000      | 2019-02-10    |
| 2  | 5          | 1600      | 2019-02-11    |
| 3  | 4          | 850       | 2019-02-11    |
| 4  | 5          | 1500      | 2019-02-13    |
+----+------------+-----------+---------------+

I need to get a list of unique product ids that are the latest (newest, in other word, bigger date) ones. So this is the expected result:

+------------+-----------+---------------+
| product_id |   price   |     date      |
+------------+-----------+---------------+
| 4          | 850       | 2019-02-11    |
| 5          | 1500      | 2019-02-13    |
+------------+-----------+---------------+

Any idea how can I achieve that?

Here is my query:

SELECT id, product_id, price, MAX(date)
FROM tbl
GROUP BY product_id
-- ot selects the max `date` with random price like this:
    +------------+-----------+---------------+
    | product_id |   price   |     date      |
    +------------+-----------+---------------+
    | 4          | 2000      | 2019-02-11    |
    | 5          | 1600      | 2019-02-13    |
    +------------+-----------+---------------+
-- See? Prices are wrong

maybe this can help you: https://stackoverflow.com/questions/5191503/how-to-select-the-last-record-of-a-table-in-sql regards — Léon Zimmermann, Feb 13 '19 at 11:55

score 3 · Answer 1 · edited Feb 13 '19 at 12:05

3

You could use a correlated subquery

select t1.* from table t1
where t1.date=( select max(date) from table t2
where t1.product_id=t2.product_id
            )

edited Feb 13 '19 at 12:05

Strawberry

33,750
13
40
57

answered Feb 13 '19 at 11:53

Zaynul Abadin Tuhin

31,407
5
33
63

Harshil Doshi · Answer 2 · 2019-02-13T12:06:07.177

1

Select *from
table1 t1
where (t1.product_id, t1.date) in 
               (select t2.product_id, max(t2.date)
                from table1 t2
                where t1.product_id = t2.product_id                    
               )

edited Feb 13 '19 at 12:06

answered Feb 13 '19 at 11:56

Harshil Doshi

3,497
3
14
37

You can remove the correlation by removing the where clause inside the subquery and use `group by product_id`. – Salman A Feb 13 '19 at 12:07
yes i can either do that or remove `group by` from inner query. I already did the later. But which one will be better according to you? – Harshil Doshi Feb 13 '19 at 12:08

score 0 · Answer 3 · answered Feb 13 '19 at 11:53

Don't use a GROUP BY. Use a filter:

SELECT id, product_id, price, MAX(date)
FROM tbl
WHERE tbl.date = (SELECT MAX(t2.date)
                  FROM tbl t2
                  WHERE t2.product_id = tbl.product_id
                 );

With an index on (product_id, date), this is probably the fastest method.

If you can have duplicates on a given date, you can resolve them with:

SELECT id, product_id, price, MAX(date)
FROM tbl
WHERE tbl.id = (SELECT t2.id
                FROM tbl t2
                WHERE t2.product_id = tbl.product_id
                ORDER BY t2.date DESC
                LIMIT 1
               );

Georgi Raychev · Answer 4 · 2019-02-13T12:17:10.460

0

Assuming that you are using a modern version of MySQL (8.0), you can use this:

select *
from (
        SELECT id
            , product_id
            , price
            , date
            , row_number() over (partition by product_id order by date desc) rn
        FROM tbl
) a
where rn = 1

edited Feb 13 '19 at 12:17

answered Feb 13 '19 at 11:56

Georgi Raychev

1,288
1
13
26

score 0 · Answer 5 · answered Feb 13 '19 at 11:59

My solution is with the analytic function first_value

SELECT distinct product_id, 
    first_value(price) over (partition by product_id order by date desc) last_price, 
    first_value(date) over (partition by product_id order by date desc) last_date
FROM tbl

How to select the latest price for product?

5 Answers5