join table with recent rows only

Question

I know there are questions on the same topic, but I couldn't get it right.

I am following this link, and tried this query:

    SELECT * FROM food_list f
    INNER JOIN (
            SELECT MAX(updated_on) max_row, rate, item_id
            FROM food_rate
            GROUP BY rate, item_id
        ) f_max ON (f_max.item_id = f.id)
    INNER JOIN food_rate fr ON (fr.updated_on = f_max.max_row);

But I am not getting the records right.

Here's my table:

food_list:

id    |    item
----------------
1     |    pizza
2     |    burger
3     |    sandwich

food_rate:

id    |   item_id   |   rate   |   updated_on
----------------------------------------------
1     |    1        |   80     |   2018-06-01
2     |    2        |   90     |   2018-06-01
3     |    3        |   70     |   2018-06-01
4     |    1        |   60     |   2018-06-02

I want to receive the records with latest date from food_rate for each item in food_list.

Here's the expected output:

item_id   |  rate   |   updated_on
----------------------------------
1         |   60    |   2018-06-02
2         |   90    |   2018-06-01
3         |   70    |   2018-06-01

score 1 · Accepted Answer · answered Jun 28 '18 at 11:38

You can get the latest rows using a correlated subquery:

select fr.*
from food_rate fr
where fr.updated_on = (select max(fr2.updated_on)
                       from food_rate fr2
                       where fr2.item_id = fr.item_id
                      );

You can add the join for the filtering:

select fr.*
from food_list fl join
     food_rate fr
     on fr.item_id = fl.id
where fr.updated_on = (select max(fr2.updated_on)
                       from food_rate fr2
                       where fr2.item_id = fr.item_id
                      );

You can use your aggregation method, but it is likely to be less efficient (given appropriate indexes) because the join on food_list presumably reduces the number of rows. You are missing a condition in the on clause:

INNER JOIN
food_rate fr
ON fr.updated_on = f_max.max_row AND fr.item_id = f_max.item_id;

score 1 · Answer 2 · answered Jun 28 '18 at 11:58

You only want to get data from the food_rate table, so there is no need to join anything. Select from food_rate where updated_on is the maximum updated_on for the item_id:

select item_id, rate, updated_on
from food_rate
where (item_id, updated_on) in
(
  select item_id, max(updated_on)
  from food_rate
  group by item_id
);

As of MySQL 8.0 you can also use a window function for this, thus reading the table only once:

select item_id, rate, updated_on
from
(
  select
    item_id, rate, updated_on,
    max(updated_on) over (partition by item_id) as max_updated_on
  from food_rate
) rates
where updated_on = max_updated_on;

join table with recent rows only

2 Answers2