I want to use direct query to pick out the latest record for a user. A user may have 5 records in the database and I want to pick the latest one. The latest one can be determined by the user Id and max mod id as there is a new id created every time a user registers for one.
Is this the most efficient query to pick the latest record
SELECT
MC.USERID
,OG.OrganisationID
, MC.Id AS ModID
, MC.TYPE AS ResID
,CAST(MC.EndDateTime AS DATETIME2) AS EndDate
,CAST(MC.ExpiryDate AS DATETIME2) ExpiryDate
,CASE
WHEN ExpiryDate >= GETDATE() AND
IsDeleted = 0 THEN 1
ELSE 0
END AS Compliance
FROM
MC
LEFT JOIN OG
ON MC.USERID = OG. USERID
INNER JOIN
(
SELECT
MC.USERID
,MAX(MC.Id) AS Max_Record
FROM MC
GROUP BY MC.USERID) Latest
ON MC.USERID = Latest.USERID
AND MC.Id = Latest.Max_Record
