Determining most recent date in a Union query

[Cameron]

i have a union query that selects certification date from a number of tables,
I have been asked by the users to only show the most recent certification
date. How would I format the following query so that it only resulted in the
most recently certified records.

SELECT EmpAwareness.EmpID, EmpAwareness.CertificationDate,
EmpAwareness.ExpiryDate, CoursesMaster.CourseTitle, EmpAwareness.SessionID,
"Awareness" AS Category
FROM CoursesMaster RIGHT JOIN EmpAwareness ON CoursesMaster.CourseID =
EmpAwareness.CourseID

Union

Select EmpFirstAid.EmpID, EmpFirstAid.CertificationDate,
EmpFirstAid.ExpiryDate, CoursesMaster.CourseTitle, EmpFirstAid.SessionID,
"FirstAid" as Category
FROM CoursesMaster RIGHT JOIN EmpFirstAid ON CoursesMaster.CourseID =
EmpFirstAid.CourseID

Union

Select EmpFunctionRelated.EmpID, EmpFunctionRelated.CertificationDate,
EmpFunctionRelated.ExpiryDate, CoursesMaster.CourseTitle,
EmpFunctionRelated.SessionID, "MillCert" as Category
FROM CoursesMaster RIGHT JOIN EmpFunctionRelated ON CoursesMaster.CourseID =
EmpFunctionRelated.CourseID

Union

Select EmpMandatory.EmpID, EmpMandatory.CertificationDate,
EmpMandatory.ExpiryDate, CoursesMaster.CourseTitle, EmpMandatory.SessionID,
"Mandatory" as Category
FROM CoursesMaster RIGHT JOIN EmpMandatory ON CoursesMaster.CourseID =
EmpMandatory.CourseID

Union

Select EmpMedical.EmpID, EmpMedical.CertificationDate,
EmpMedical.ExpiryDate, CoursesMaster.CourseTitle, EmpMedical.SessionID,
"Medical" as Category
FROM CoursesMaster RIGHT JOIN EmpMedical ON CoursesMaster.CourseID =
EmpMedical.CourseID

Union

Select EmpOES.EmpID, EmpOES.CertificationDate, EmpOES.ExpiryDate,
CoursesMaster.CourseTitle, EmpOES.SessionID, "OES" as Category
FROM CoursesMaster RIGHT JOIN EmpOES ON CoursesMaster.CourseID =
EmpOES.CourseID

Union

Select EmpOppBlocks.EmpID, EmpOppBlocks.CertificationDate,
EmpOppBlocks.ExpiryDate, CoursesMaster.CourseTitle, EmpOppBlocks.SessionID,
"OppBlocks" as Category
FROM CoursesMaster RIGHT JOIN EmpOppBlocks ON CoursesMaster.CourseID =
EmpOppBlocks.CourseID

Union

Select EmpOrientation.EmpID, EmpOrientation.CertificationDate,
EmpOrientation.ExpiryDate, CoursesMaster.CourseTitle,
EmpOrientation.SessionID, "Orientation" as Category
FROM CoursesMaster RIGHT JOIN EmpOrientation ON CoursesMaster.CourseID =
EmpOrientation.CourseID

Union

Select EmpPersonal.EmpID, EmpPersonal.CertificationDate,
EmpPersonal.ExpiryDate, CoursesMaster.CourseTitle, EmpPersonal.SessionID,
"Personal" as Category
FROM CoursesMaster RIGHT JOIN EmpPersonal ON CoursesMaster.CourseID =
EmpPersonal.CourseID

UNION Select EmpTrades.EmpID, EmpTrades.CertificationDate,
EmpTrades.ExpiryDate, CoursesMaster.CourseTitle, EmpTrades.SessionID,
"Trades" as Category
FROM CoursesMaster RIGHT JOIN EmpTrades ON CoursesMaster.CourseID =
EmpTrades.CourseID;

 

[Jerry Whittle]

SELECT TOP 1 *
From (SELECT EmpAwareness.EmpID, EmpAwareness.CertificationDate,
EmpAwareness.ExpiryDate, CoursesMaster.CourseTitle, EmpAwareness.SessionID,
"Awareness" AS Category
FROM CoursesMaster RIGHT JOIN EmpAwareness ON CoursesMaster.CourseID =
EmpAwareness.CourseID
Union
Select EmpFirstAid.EmpID, EmpFirstAid.CertificationDate,
EmpFirstAid.ExpiryDate, CoursesMaster.CourseTitle, EmpFirstAid.SessionID,
"FirstAid" as Category
FROM CoursesMaster RIGHT JOIN EmpFirstAid ON CoursesMaster.CourseID =
EmpFirstAid.CourseID
Union
Select EmpFunctionRelated.EmpID, EmpFunctionRelated.CertificationDate,
EmpFunctionRelated.ExpiryDate,
CoursesMaster.CourseTitle, EmpFunctionRelated.SessionID,
"MillCert" as Category
FROM CoursesMaster RIGHT JOIN EmpFunctionRelated ON CoursesMaster.CourseID =
EmpFunctionRelated.CourseID
Union
Select EmpMandatory.EmpID, EmpMandatory.CertificationDate,
EmpMandatory.ExpiryDate, CoursesMaster.CourseTitle, EmpMandatory.SessionID,
"Mandatory" as Category
FROM CoursesMaster RIGHT JOIN EmpMandatory ON CoursesMaster.CourseID =
EmpMandatory.CourseID
Union
Select EmpMedical.EmpID, EmpMedical.CertificationDate,
EmpMedical.ExpiryDate, CoursesMaster.CourseTitle, EmpMedical.SessionID,
"Medical" as Category
FROM CoursesMaster RIGHT JOIN EmpMedical ON CoursesMaster.CourseID =
EmpMedical.CourseID
Union
Select EmpOES.EmpID, EmpOES.CertificationDate, EmpOES.ExpiryDate,
CoursesMaster.CourseTitle,
EmpOES.SessionID, "OES" as Category
FROM CoursesMaster RIGHT JOIN EmpOES ON CoursesMaster.CourseID =
EmpOES.CourseID
Union
Select EmpOppBlocks.EmpID, EmpOppBlocks.CertificationDate,
EmpOppBlocks.ExpiryDate, CoursesMaster.CourseTitle, EmpOppBlocks.SessionID,
"OppBlocks" as Category
FROM CoursesMaster RIGHT JOIN EmpOppBlocks ON CoursesMaster.CourseID =
EmpOppBlocks.CourseID
Union
Select EmpOrientation.EmpID, EmpOrientation.CertificationDate,
EmpOrientation.ExpiryDate, CoursesMaster.CourseTitle,
EmpOrientation.SessionID, "Orientation" as Category
FROM CoursesMaster RIGHT JOIN EmpOrientation ON CoursesMaster.CourseID =
EmpOrientation.CourseID
Union
Select EmpPersonal.EmpID, EmpPersonal.CertificationDate,
EmpPersonal.ExpiryDate, CoursesMaster.CourseTitle, EmpPersonal.SessionID,
"Personal" as Category
FROM CoursesMaster RIGHT JOIN EmpPersonal ON CoursesMaster.CourseID =
EmpPersonal.CourseID
UNION
Select EmpTrades.EmpID, EmpTrades.CertificationDate,
EmpTrades.ExpiryDate, CoursesMaster.CourseTitle, EmpTrades.SessionID,
"Trades" as Category
FROM CoursesMaster RIGHT JOIN EmpTrades ON CoursesMaster.CourseID =
EmpTrades.CourseID)
ORDER BY CertificationDate DESC;

However with all the UNIONs and similar table structures, your root problem
is that the data isn't properly normalized.

 

[Cameron]

Yes, this database is not normalized. Actually, prior to assuming the role of
developer for this project I asked the preveous developer if they had built
the database using proper design techniques and assured normailzation. This
drew a blank.

I expected to get this build to work for the meantime, and a new system is
in the development stag using SQL server and proper data build proceedures.

Thank you for the responce. I will give it a try.