Question 237748 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to Get only recent record from the Search Query

I have a search query where I am getting records from 2 tables

Table A gives client data , Table B gives transaction number

clientdid is PK in table A and FK in table B . I am using left join to get the data

QUERY

select a.clientdid, a.clientcode, a.name, b.transactionnumber
from table a
  left join table b on (a.clientdid = b.id )

Output is coming like this .

clientdid clientcode name transactionnumber
100 1 CLIENTA S5221S/SO/1062
100 1 CLIENTA S5221S/SO/1063

Expected is

clientdid clientcode name transactionnumber
100 1 CLIENTA S5221S/SO/1063

How can I only get the recent record. I prefer not to use the subquery as this is just one small part

Group concat but didnt get the desired output

Tags:

2

Answers


  1. 0

    You can join with b again that assumed to have a newer transaction and filtered for that being false. Only the newest transaction per client will suffice that:

    select a.clientdid, a.clientcode, a.name, b.transactionnumber
from table a
left join table b on (a.clientdid = b.id ) 
left join table b b_older on (a.clientid = b_older.id and b.transactionnumber < b_older.transactionnumber)
where b_older.id is null
    Login or Signup to reply.
  2. 0

    you can also try with rank:

    select a.clientdid, a.clientcode, a.name, b.transactionnumber, 
rank() over(partition by a.clientdid order by b.transactionnumber desc) as rn
from table a
left join table b on (a.clientdid = b.id )  
qualify rn = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search