Perform JOIN only on the last returned row in MYSQL

Question

I have table t1 which stores all requests. I have table t2 which stores audits related to requests made in table t1. In order to get latest audits for every request I need to perform join on the two tables.

I am able to perform JOIN using the following query:

SELECT 
    t2.id, t1.name, 
    t2.Msg
FROM 
    requests t1
    LEFT JOIN audits t2 ON t1.AuditId = t2.AuditId
ORDER BY t2.id DESC;

The above query is returning result as below:

id          Name            Msg      
56895415    ABC05           Message5
56895414    ABC05           Message4
56895413    ABC05           Message3
56895303    ABC04           Message5
56895302    ABC04           Message4
56895301    ABC04           Message3

I want to modify the query such that only the last row(with highest id value) is shown for every t1.name In other words, my output should be as below:

id          Name            Msg      
56895415    ABC05           Message5
56895303    ABC04           Message5

Do you have a date/time indicator on audits table or some sort of row sequence which would indicate msg5 is the most recent? (assuming you want most recent?) — xQbert, Mar 16 '20 at 16:20
The `id` in `table t1` provide row sequence where in the highest value of `id` for every `Name` has the latest message — meallhour, Mar 16 '20 at 16:28

score 0 · Answer 1 · answered Mar 16 '20 at 16:16

0

You can rewrite the query like below

SELECT 
    t2.id, t1.name, 
    t2.Msg
FROM 
    requests t1
    LEFT JOIN (SELECT id, Msg FROM audits ORDER BY id DESC LIMIT 1) t2 ON t1.AuditId = t2.AuditId
ORDER BY t2.id DESC;

answered Mar 16 '20 at 16:16

webzar

104
8

I think this would need to use a correlated sub-query in order to get all the auditIDs since requests could have audit 1,2,3 etc... but the derived table you're building would only return 1 row unless mysql supports an outer apply. using a left join that I don't know about. – xQbert Mar 16 '20 at 16:22
This is not working as I am getting `null` for `id` and `Msg` – meallhour Mar 16 '20 at 16:25
`id Name Msg null ABC05 null null ABC04 null` – meallhour Mar 16 '20 at 16:27
Derived table will act as a subquery when using with JOIN. It will always get the latest record for that particular AuditId – webzar Mar 16 '20 at 16:30
@meallhour can you please post your query dump here. As it should work! – webzar Mar 16 '20 at 16:30
You will get NULL only when the audit is not found for the request – webzar Mar 16 '20 at 16:31
`SELECT t2.id, t1.name, t2.Msg FROM requests t1 LEFT JOIN (SELECT id, AuditId, Msg FROM audits ORDER BY id DESC LIMIT 1) t2 ON t1.AuditId = t2.AuditId ORDER BY t2.id DESC;` It should not be `NULL` as we have audits for all requests – meallhour Mar 16 '20 at 16:37
Sorry i mean to say your table sql dump here so i can test locally – webzar Mar 16 '20 at 16:39
I have already given sample dump from my table in my question. `id Name Msg 56895415 ABC05 Message5 56895414 ABC05 Message4 56895413 ABC05 Message3 56895303 ABC04 Message5 56895302 ABC04 Message4 56895301 ABC04 Message3` – meallhour Mar 16 '20 at 16:40
Also, `t2.id` is primary key and it can never be null – meallhour Mar 16 '20 at 16:50

xQbert · Answer 2 · 2020-03-16T16:59:47.647

couple approaches:

Syntax on the below has not been verified: This is just "close" to what I'm thinking. I'd need to recreate your tables/data to verify.

Get unique max ID's for each AuditID and join back to your query:

SELECT 
    t2.id, t1.name, 
    t2.Msg
FROM (SELECT max(ID) MID, AuditID
      FROM requests
      INNER JOIN Audits
      GROUP BY AuditID) t3
    INNER JOIN requests t1
       ON t3.AuditID= T1.AuditID
    LEFT JOIN audits t2 
       ON t1.AuditId = t2.AuditId
      AND T3.MID = T2.ID
ORDER BY t2.id DESC;

The other is to use a rowNumber variable per name sorting by ID descending then select only row 1 for each name. Use this as your t2 and then join. Depending on version of mySQL the analytic for this would be:

SELECT 
    t2.id, 
    t1.name, 
    t2.Msg
FROM requests t1
LEFT JOIN  (Select AuditID, msg, id, row_number() over (partition by AuditID ORDER BY id desc) rn) t2
  ON t1.AuditId = t2.AuditId
 and RN = 1
ORDER BY t2.id DESC;

but only 8.0+ I believe supports this syntax.

I am getting an error using the first approach - Error Code: 14. Can't change size of file (OS errno 28 - No space left on device) — meallhour, Mar 16 '20 at 20:47

Perform JOIN only on the last returned row in MYSQL

2 Answers2