MySQL get last record for each item

Question

I've been at it for two days, can't figure it out. Hope someone can help me.

I have several tables with one to many relationships.

It is hard to explain, so here are examples of tables concerned (file id comes from another table and is not related to this problem, file id we looking for in this example is 1)

Table 1 - history
+----+---------+---------+-----------+---------------------+
| id | file_id | user_id | action_id | datetime            |
+----+---------+---------+-----------+---------------------+
|  1 |       1 |       1 |         1 | 2014-02-19 19:19:49 |
|  2 |       1 |       1 |         2 | 2014-02-19 19:20:06 |
|  3 |       1 |       1 |         3 | 2014-02-19 19:32:09 |
|  4 |       2 |       2 |         1 | 2014-02-20 11:52:59 |
|  5 |       2 |       2 |         2 | 2014-02-20 11:53:08 |
+----+---------+---------+-----------+---------------------+

Table 2 - file_items
+----+---------+------------+---------------+
| id | file_id | item_id    | remark        |
+----+---------+------------+---------------+
|  1 |       1 |          1 | item1 remark  |
|  2 |       1 |         20 | item2 remarks |
|  3 |       2 |          2 | test          |
+----+---------+------------+---------------+

Table 3 - item_statuses
+----+----------------+--------------+----------------+----------------------+
| id | file_action_id | file_item_id | item_status_id | comment              |
+----+----------------+--------------+----------------+----------------------+
|  1 |              2 |            1 |              1 |                      |
|  2 |              2 |            2 |              1 |                      |
|  3 |              3 |            1 |              2 | status comment       |
|  4 |              3 |            2 |              1 | item2 status comment | 
|  5 |              5 |            3 |              1 |                      |
+----+----------------+--------------+----------------+----------------------+

t1.action_id is related to t3.file_action_id

t2.item_id is related to t3.file_item_id

t1.file_id is related to t2.file_id

I am trying to get last status for each item for a specific file (file id 1 in this case).

Desired result:

+----------------+-----------------+------------+---------------------+----------------+----------------------+
| file_action_id | file_item_id | remark        | file_item_status_id | item_status_id | COMMENT              |
+----------------+-----------------+------------+---------------------+----------- ----+----------------------+
|              3 |            1 | item1 remark  |                   3 |              2 | status comment       |
|              3 |            2 | item2 remarks |                   4 |              1 | item2 status comment |
+----------------+--------------+---------------+---------------------+----------------+----------------------+

What I get:

+----------------+-----------------+------------+---------------------+----------------+----------------------+
| file_action_id | file_item_id | remark        | file_item_status_id | item_status_id | COMMENT              |
+----------------+-----------------+------------+---------------------+----------  -----+---------------------+
|              3 |            1 | item1 remark  |                   1 |              1 |                      |
|              3 |            1 | item1 remark  |                   3 |              2 | status comment       |
|              3 |            2 | item2 remarks |                   2 |              1 |                      |
|              3 |            2 | item2 remarks |                   4 |              1 | item2 status comment |
+----------------+--------------+---------------+---------------------+----------------+----------------------+

Query:

SELECT
t1.id AS file_action_id,
t2.id AS file_item_id,
t2.remark AS remark,
t5.id AS file_item_status_id,
t5.item_status_id AS item_status_id,
t5.comment AS COMMENT

FROM `file_history` AS t1

LEFT JOIN `file_items` AS t2
ON (t1.file_id = t2.file_id)

LEFT JOIN `file_item_statuses` AS t5
ON (t2.id = t5.file_item_id)

WHERE t1.file_id = 1
AND
t1.id = (SELECT MAX(id) FROM `file_history` WHERE file_id = 1)

I tried using GROUP BY and ORDER BY but it didn't do it for me.

SQL FIDDLE HERE

score 1 · Accepted Answer · edited May 23 '17 at 12:20

Ok, so you want the latest status for each item for a specific file, so, what I would do, is do the INNER JOIN with a table which only would have the latest status.

For doing it, instead of LEFT JOIN with file_item_statuses, I will LEFT JOIN with a subquery with the right data (the subquery will get you only the rows with maximum status id value, I checked this to achieve it: SQL Select only rows with Max Value on a Column)

SELECT fs.id          
     , fs.file_item_id
     , fs.item_status_id
     , fs.comment  
FROM file_item_statuses fs
INNER JOIN (
    SELECT MAX(id) AS id
    FROM file_item_statuses
    GROUP BY file_item_id
    ) maxfs 
ON maxfs.id = fs.id

And this subquery, you have to LEFT JOIN it with the main:

SELECT
t1.id AS file_action_id,
t2.id AS file_item_id,
t2.remark AS remark,
t5.id AS file_item_status_id,
t5.item_status_id AS item_status_id,
t5.comment AS COMMENT

FROM `file_history` AS t1

LEFT JOIN `file_items` AS t2
ON (t1.file_id = t2.file_id)

INNER JOIN ( 
    SELECT fs.id          
         , fs.file_item_id
         , fs.item_status_id
         , fs.comment  
    FROM file_item_statuses fs
    INNER JOIN (
        SELECT MAX(id) AS id
        FROM file_item_statuses
        GROUP BY file_item_id
    ) maxfs 
    ON maxfs.id = fs.id ) AS t5
ON (t2.id = t5.file_item_id)

WHERE t1.id = (SELECT MAX(id) FROM `file_history` WHERE file_id = 1) 

GROUP BY file_item_id

I run it and throw exactly the results you wanted, enjoy!

Thanks, works great! the other answer works too. I don't have much data to test the efficiency. I'd like to mark the more efficient one as the answer as they both work. — B-and-P, Feb 20 '14 at 09:22
Only way to know it: Run both in your machine after inserting hundred of dummy data and check what is faster. Anyway, In the subquery, you may change the way I provided (first option from the answer) with the second option from the answer I checked to achieve it. That way you will be able of improve it later. Ah! and don't forget include proper indexes! (build them with the fields you use for the joins) — Federico J., Feb 20 '14 at 10:04

Leo Zhao · Answer 2 · 2014-02-20T08:26:51.093

Try this:

SELECT
t1.id AS file_action_id,
t2.id AS file_item_id,
t2.remark AS remark,
t5.id AS file_item_status_id,
t5.item_status_id AS item_status_id,
t5.comment AS COMMENT


FROM `file_history` AS t1

LEFT JOIN `file_items` AS t2
ON (t1.file_id = t2.file_id)

LEFT JOIN `file_item_statuses` AS t5
ON (t2.id = t5.file_item_id)

WHERE t1.file_id = 1
AND
t1.id = (SELECT MAX(id) FROM `file_history` WHERE file_id = 1)
# Add the follow condition to fecth max id in file_item_statuses of each item
# vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
and t5.id in (SELECT MAX(t3.id) 
            FROM `file_item_statuses` t3, `file_items` t4 
            WHERE t4.id = t3.file_item_id 
            AND t4.file_id = 1 
            GROUP BY t4.id)
#GROUP BY t2.id
#ORDER BY MAX(file_item_status_id)

SQL Fiddle

Be careful with the IN in the WHERE clauses, it uses to be a high resource consumer: When you'll have more than 1000 results in there is going to be quite slow. I used it in a WHERE in a SQL to retrieve only posts from friends in a social network and I had to change it because when someone had more than 300 friends it slowed crazily the query — Federico J., Feb 20 '14 at 10:08

score 0 · Answer 3 · answered Feb 20 '14 at 07:45

try this query

SELECT
t1.id AS file_action_id,
t2.id AS file_item_id,
t2.remark AS remark,
t5.id AS file_item_status_id,
t5.item_status_id AS item_status_id,
t5.comment AS COMMENT


FROM `file_history` AS t1

LEFT JOIN `file_items` AS t2
ON (t1.file_id = t2.file_id)

LEFT JOIN `file_item_statuses` AS t5
ON (t2.id = t5.file_item_id)

WHERE t1.file_id = 1
AND
t1.id = (SELECT MAX(id) FROM `file_history` WHERE file_id = 1)
GROUP BY t1.id,t2.id

MySQL get last record for each item

3 Answers3