Mysql Join get Latest value of each group

Question

Mysql version: 8.0.21

I am lookig of get the latest value of each "TableData" which has the type "fruit".

Table Name: TableNames
 _________________________________________
| id | name         | id_group  |   type  |
|-----------------------------------------| 
| 0  | AppleGroup   | apple     |  fruit  |
| 1  | BananaGroup  | banana    |  fruit  |
| 2  | OtherGroup   | other     |  other  |


Table Name: TableData
 __________________________
| id |  id_group  | value  | 
|--------------------------| 
| 0  | apple      |  12    |
| 1  | banana     |  8     |
| 2  | apple      |  3     | <--get latest
| 3  | banana     |  14    |
| 4  | banana     |  4     | <--get latest

With this Query I get all the items, but I am looking for the lastest of each. I already tried to group by and order by, but the problem is that I first need to order by and then group by, seems that's not possible in Mysql.

SELECT 
  n.name,
  d.value
  FROM TableNames n
  INNER JOIN
  (
    SELECT *
    FROM TableData
  ) d ON d.`id_group` = n.`id_group` 
  WHERE type = 'fruit'

Expected ouput:
 _____________________
| name        | value |     
|---------------------|
| AppleGroup  | 3     |
| BananaGroup | 4     |

score 1 · Answer 1 · answered Jan 21 '23 at 12:40

1

Without ROW_NUMBER(), because you can be on an older version of MySQL (before 8.0), you can create an inner join with the max(id):

SELECT 
  TableNames.name,
  TableData.value
FROM
    TableData
INNER JOIN (
  SELECT 
    id_group,
    MAX(id) as max
  FROM TableData
  GROUP BY id_group) x  ON x.id_group = TableData.id_group
INNER JOIN TableNames on TableNames.id_group = TableData.id_group
WHERE x.max = TableData.id

see: DBFIDDLE

answered Jan 21 '23 at 12:40

Luuk

12,245
5
22
33

That did the trick, still to much to learn. thank you so much – johnnytolengo Jan 22 '23 at 18:49

Tim Biegeleisen · Answer 2 · 2023-01-22T12:20:29.200

0

On MySQL 8+, we can use ROW_NUMBER():

WITH cte AS (
    SELECT tn.name, tn.id_group, td.value,
           ROW_NUMBER() OVER (PARTITION BY td.id_group ORDER BY td.id DESC) rn
    FROM TableNames tn
    INNER JOIN TableData td
        ON td.id_group = tn.id_group
    WHERE tn.type = 'fruit'
)

SELECT name, value
FROM cte
WHERE rn = 1
ORDER BY id_group;

For optimization, you may consider adding the following index to the TableData table:

CREATE INDEX idx ON TableData (id_group);

Note that on InnoDB, MySQL will automatically include id at the end of the index, hence the index is (id_group, id). This should let MySQL efficiently do the join in the CTE and also compute ROW_NUMBER.

edited Jan 22 '23 at 12:20

answered Jan 21 '23 at 12:14

Tim Biegeleisen

502,043
27
286
360

it's working, you save my day. it's any chance to do the same query but avoiding to collect that big buffer from the first Select inside the "cte" ? – johnnytolengo Jan 22 '23 at 11:35
@johnnytolengo It is not a buffer so much as a necessary subquery (any method you use will require a subquery...somewhere). – Tim Biegeleisen Jan 22 '23 at 11:44
The problem becomes when the TableData gets too large (+million rows) the Mysql timeouts because the SELECT inside the cte search every single row. Maybe it's another efficient way to do so.. – johnnytolengo Jan 22 '23 at 12:11
Try adding a relevant index, q.v. my updated answer above. – Tim Biegeleisen Jan 22 '23 at 12:20

Mysql Join get Latest value of each group

2 Answers2