I am using Left join with other table . There are multiple rows with the same p_id on other table . I want the row with latest p_id

Question

I am using Left join( on A.a_id = B.p_id ) for joining tables A and B .There are multiple rows with same p_id on the other table. I want the row with latest p_id .

The column joining table A with table B is a_id and p_id . I want to JOIN both the table and group the records and only want table B record with max bid . Can anyone help me with mysql query finding the desired result . I have posted the desired result below .

Mysql query :

 Select * from A Left JOIN B ON A.a_id =B.p_id group by p_id 
 having max(b_id)

Table A

a_id  column1 
 1   Adam
 2   Voge

Table B

b_id  p_id   column2
 1   1    dash
 2   1    Hash
 3   2    kyu

Desired Result should look like this

a_id b_id   column1 column2
 1   2      Adam    Hash 
 2   3      Voge    kyu

Answer 1

@surya singh..

with joined_data as (
select 
a.a_id,a.column1,b.p_id,b.column2
from tablea a
inner join tableb b on a.a_id= b.b_id
),
find_recent_data as (
select 
a_id,column1,
column2,
row_number() over (partition by a_id order by p_id desc)  key_column
from joined_data)
select * from find_recent_data 
where key_column=1;