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.

Cr Bhargavi · Answer

select t1.SerialNumber,t2.CertifiedDate as LastCertifiedDate,t2.CertifiedBy as LastCertifiedBy,t1.Flag from tblMaster t1 Left join ( select *,Row_Number() over(partition by SerialNumber order by CertifiedDate desc)RN from tblTransaction )t2 on t1.SerialNumber=t2.SerialNumber where RN=1

Arjun Karthikeyan S · Answer

SELECT m.SrNo, m.SerialNumber, t.CertifiedOn AS "Last Certified Date", t.CertifiedBy AS "Last Certified By", m.Flag FROM [Master Table] m LEFT JOIN ( SELECT SerialNumber, MAX(CertifiedOn) AS MaxCertifiedDate FROM [Transaction Table] GROUP BY SerialNumber ) t ON m.SerialNumber = t.SerialNumber AND t.MaxCertifiedDate = t.CertifiedOn You can use this query for the mentioned output.

Tuhin Paul · Answer

SELECT m.SrNo, m.SerialNumber, t.CertifiedOn AS "Last Certified Date", t.CertifiedBy AS "Last Certified By", m.Flag FROM [Master Table] m LEFT JOIN ( SELECT SerialNumber, MAX(CertifiedOn) AS MaxCertifiedDate FROM [Transaction Table] GROUP BY SerialNumber ) t ON m.SerialNumber = t.SerialNumber WHERE t.MaxCertifiedDate IS NOT NULL; The Where clause filters out rows where t.MaxCertifiedDate is NULL, keeping that only the latest certified records are included in the result set.

Sachin Singh · Answer

There are multiple ways, select t1.SerialNumber,t2.CertifiedDate as LastCertifiedDate,t2.CertifiedBy as LastCertifiedBy,t1.Flag from tblMaster t1 Left join ( select *,Row_Number() over(partition by SerialNumber order by CertifiedDate desc)RN from tblTransaction )t2 on t1.SerialNumber=t2.SerialNumber where RN=1

Deepak Rawat · Answer

You can try this: SELECT m.SrNo, m.[Serial Number], t.[CertifiedOn] AS 'Last Certified Date', t.[CertifiedBy] AS 'Last Certified By', m.Flag FROM [Master Table] m LEFT JOIN ( SELECT [SerialNumber], MAX([CertifiedOn]) AS 'MaxCertifiedDate' FROM [Transaction Table] GROUP BY [SerialNumber] ) mt ON m.[Serial Number] = mt.[SerialNumber] LEFT JOIN [Transaction Table] t ON m.[Serial Number] = t.[SerialNumber] AND mt.MaxCertifiedDate = t.[CertifiedOn]

Amit Mohanty · Answer

SELECT mt.SrNo, mt.SerialNumber, tt.CertifiedOn AS "Last Certified Date", tt.CertifiedBy AS "Last Certified By", mt.Flag FROM MasterTable mt LEFT JOIN ( SELECT SerialNumber, CertifiedOn, CertifiedBy FROM TransactionTable tt WHERE CertifiedOn = ( SELECT MAX(CertifiedOn) FROM TransactionTable WHERE SerialNumber = tt.SerialNumber ) ) tt ON mt.SerialNumber = tt.SerialNumber

Prasad Raveendran · Answer

You can improve the query using a Common Table Expression (CTE) to simplify the logic and potentially improve performance. WITH maxDates AS ( SELECT SerialNumber, MAX(CertifiedOn) AS MaxCertifiedOn FROM [Transaction Table] GROUP BY SerialNumber ) SELECT m.SrNo, m.SerialNumber, t.CertifiedOn AS LastCertifiedDate, t.CertifiedBy AS LastCertifiedBy, m.Flag FROM [Master Table] m LEFT JOIN maxDates ON m.SerialNumber = maxDates.SerialNumber LEFT JOIN [Transaction Table] t ON t.SerialNumber = maxDates.SerialNumber AND t.CertifiedOn = maxDates.MaxCertifiedOn Ensure that you replace [Master Table] and [Transaction Table] with the actual table names in your database. It's recommended to analyze the query execution plan and monitor the performance metrics to evaluate the effectiveness of this CTE-based approach in your specific scenario

Anandu G Nath · Answer

SELECT T1.SrNo, T1.SerialNumber, t2.CertifiedOn AS "Last Certified Date", t2.CertifiedBy AS "Last Certified By", T1.Flag FROM [Master Table] T1 LEFT JOIN ( SELECT SerialNumber, MAX(CertifiedOn) AS MaxCertifiedDate FROM [Transaction Table] GROUP BY SerialNumber ) t2 ON m.SerialNumber = t2.SerialNumber WHERE t2.MaxCertifiedDate IS NOT NULL;