I want to use a 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

​I want to use a 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 FROMMCLEFT 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  Read More

​