With	e (Inspection_Date, Formatted_Licence_Number, Inspections_Facts_Key, id)
AS	(
	
	SELECT	Inspection_Date, Formatted_Licence_Number, t2.Inspections_Facts_Key,
		RowNumber() OVER (partition by Inspection_Date, Formatted_Licence_Number ORDER BY Inspection_Date DESC) as id
	FROM	Infodbo.Licensing_Facts t1
	INNER JOIN
		Infodbo.Inspections_Facts t2
	ON 	t1.Licensing_Facts_Key = t2.Licensing_Facts_Key

	WHERE 	t1.Type_Code = 'fp' 
	AND 	t1.Class_Code = 'health' 
	AND 	t2.Result_Code IS NOT NULL
)

SELECT	Inspection_Date, Formatted_Licence_Number, Inspections_Facts_Key
FROM	e
WHERE	id = 1;

SELECT	Inspection_Date, Formatted_Licence_Number, t2.Inspections_Facts_Key,
FROM	Infodbo.Licensing_Facts t1
INNER JOIN
	Infodbo.Inspections_Facts t2
ON 	t1.Licensing_Facts_Key = t2.Licensing_Facts_Key
INNER JOIN (
	SELECT	Formatted_Licence_Number, MAX(Inspection_Date) dt
	FROM	Infodbo.Licensing_Facts t1
	INNER JOIN
		Infodbo.Inspections_Facts t2
	ON 	t1.Licensing_Facts_Key = t2.Licensing_Facts_Key
	GROUP BY
		Formatted_Licence_Number
) x
On	x.Formatted_Licence_Number = t1.Formatted_Licence_Number
And	x.dt = t2.Inspection_Date
WHERE 	t1.Type_Code = 'fp' 
AND 	t1.Class_Code = 'health' 
AND 	t2.Result_Code IS NOT NULL;

SELECT Date,Formatted_Licence_Number,Inspections_Facts_Key
FROM
(
SELECT  ROW_NUMBER() OVER(PARTITION BY Formatted_Licence_Number ORDER BY Inspection_Date DESC) AS Seq,
Inspection_Date AS Date,
Formatted_Licence_Number,
t2.Inspections_Facts_Key

FROM
Infodbo.Licensing_Facts t1
INNER JOIN Infodbo.Inspections_Facts t2
ON t1.Licensing_Facts_Key=t2.Licensing_Facts_Key


WHERE t1.Type_Code = 'fp' 
AND t1.Class_Code = 'health' 
AND t2.Result_Code IS NOT NULL
)t
WHERE Seq=1
ORDER BY Formatted_Licence_Number