Mysql Join get Latest value of each group-CodePudding

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     |

CodePudding user response：

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

CodePudding user response：

On MySQL 8 , we can use ROW_NUMBER():

WITH cte AS (
    SELECT tn.name, tn.id_group, td.value,
           ROW_NUMBER() OVER (PARTITION BY tn.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;