Direct query

[Dev-13]

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

[Anonymous]

Hi @Dev-13 

The query you write is right, did you want to translate it to M code, if you want to translate it to M code, can you provide some sample data that can provide more suggestion for you.

Best Regards!

Yolo Zhu