SQL Query - Get All Records From Master Table

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;

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.

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

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

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]

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. 

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