SQL Query - Get All Records From Master Table

Question

Hello Team,

I have 2 tables - 1 - Master Table and 2 - Transaction Table.

And I need the output as follow -

Table – Master Table

SrNo	Asset Code	Serial Number	Flag
1	A173947	YTUII	1
2	A274307	IKJYF	1
3	A3973249723	HBYTF	1
4	A439247	OKLIYTF	1
5	A523964	YHTFCC	1
6	A6862346	KIIUTF	1
7	A788766	HGFTNH	1
8	A87235	AWRJF	1
9	A9329646	AKDIIR	1
10	A10923747	AJNIIHEW	1

Table – Transaction Table

SrNo	SerialNumber	CertifiedOn	CertifiedBy	Flag
1	YTUII	01-Jan-2023	Akash	1
2	IKJYF	01-Jan-2023	Alok	1
3	YTUII	01-Mar-2023	Pankaj	1
4	YTUII	01-Apr-2023	Shaurya	1
5	IKJYF	01-Feb-2023	Sujit	1
6	IKJYF	01-May-2023	Arnab	1
7	KIIUTF	01-Feb-2023	Ashish	1
8	KIIUTF	01-Jun-2023	Prakash	1
9	AWRJF	01-Jul-2023	Lalit	1
10	AJNIIHEW	01-Jan-2023	Lily	1

Output –

SrNo	Serial Number	Last Certified Date	Last Certified By	Flag
1	YTUII	01-Apr-2023	Shaurya	1
2	IKJYF	01-May-2023	Arnab	1
3	HBYTF	NULL	NULL
4	OKLIYTF	NULL	NULL
5	YHTFCC	NULL	NULL
6	KIIUTF	01-Jun-2023	Prakash	1
7	HGFTNH	NULL	NULL
8	AWRJF	01-Jul-2023	Lalit	1
9	AKDIIR	NULL	NULL
10	AJNIIHEW	01-Jan-2023	Lily	1

From transcation table - Get all records with Maximum Date, and if the serial number is not there, then null values.

Please help.