CODEWITHSUNDEEP
mysqlsqljoinselectgreatest-n-per-group
far1023
far1023

Reputation: 123

MySQL join table like concat but only pick latest row

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

Upvotes: 1

Views: 62

Answers (2)

Belal
Belal

Reputation: 11

  1. First you find latest created_at value from B table

Example : SELECT MAX(created_at) FROM B GROUP BY a_id;

  1. Then use first one result in below query and you get your desired output.

    FROM A 
JOIN B ON A.a_id = B.a_id 
WHERE B.created_at IN (SELECT MAX(created_at) FROM B GROUP BY a_id);

Upvotes: 1

GMB
GMB

Reputation: 222472

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

Upvotes: 2

Related Questions