Sub query

Copper Contributor

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

1 Reply
Yes, it's regular way. Indexes my provide higher efficient.