Question 21150 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql Join get Latest value of each group

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     |
Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search