MySQL join table like concat but only pick latest row

Question

I have case where I need latest status. There are two tables, I simplify my table so it looks like below:

---

Table A
+------------+
|  a_id      |
|------------|
|  A1        |
|  A2        |
+------------+

Table B
+------------+-------------+------------------+------------------+
|  b_id      |  a_id       |  status          |  created_at      |
|------------+-------------+------------------+------------------|
|  B01       |  A1         |  something       |  2020-03-14      |
|  B02       |  A1         |  something else  |  2020-04-15      |
|  B03       |  A2         |  anything        |  2020-03-22      |
+------------+-------------+------------------+------------------+

---

I want to show table from A with join table B so it will show like this:

---

+------------+--------------------+-----------------+
|  a.a_id    |  b.status          |  b.created_at   |
|------------+--------------------+-----------------|
|  A1        |  something else    |  2020-04-15     |
|  A2        |  anything          |  2020-03-22     |
+------------+--------------------+-----------------+

---

I will appreciate Mysql query or codeigniter query builder. Thank you

Answer 1

If you are running MySQL 8.0, you can do this with row_number():

select a.a_id, b.status, b.created_at
from tablea a
inner join (
    select 
        b.*, 
        row_number() over(partition by a_id order by created_at desc) rn
    from tableb b
) b on a.a_id = b.a_id and b.rn = 1

In earlier versions, one option is to filter with a correlated subquery:

select a.a_id, b.status, b.created_at
from tablea a
inner join tableb b on a.a_id = b.a_id
where b.created_at = (
    select max(b1.created_at) from tableb b1 where b1.a_id = b.a_id
)

For performance with the correlated subquery solution, consider an index on tableb(a_id, created_at).