Sub query

Question

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.&nbsp;&nbsp;Is this the most efficient query to pick the latest record&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp; &nbsp;MC.USERID&nbsp;&nbsp;&nbsp;,OG.OrganisationID&nbsp;&nbsp;&nbsp;,&nbsp;MC.Id&nbsp;AS&nbsp;ModID&nbsp;&nbsp;&nbsp;,&nbsp;MC.TYPE&nbsp;AS&nbsp;ResID&nbsp;&nbsp;&nbsp;,CAST(MC.EndDateTime&nbsp;AS&nbsp;DATETIME2)&nbsp;AS&nbsp;EndDate&nbsp;&nbsp;&nbsp;,CAST(MC.ExpiryDate&nbsp;AS&nbsp;DATETIME2)&nbsp;ExpiryDate&nbsp;&nbsp;&nbsp;,CASE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN&nbsp;ExpiryDate&nbsp;&gt;=&nbsp;GETDATE()&nbsp;AND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IsDeleted&nbsp;=&nbsp;0&nbsp;THEN&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END&nbsp;AS&nbsp;Compliance&nbsp;FROMMCLEFT&nbsp;JOIN&nbsp;OG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON&nbsp;MC.USERID&nbsp;=&nbsp;OG.&nbsp;USERID&nbsp;INNER&nbsp;JOIN&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; MC.USERID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;,MAX(MC.Id)&nbsp;AS&nbsp;Max_Record&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;MC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GROUP&nbsp;BY&nbsp;MC.USERID)&nbsp;Latest&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON&nbsp;MC.USERID&nbsp;=&nbsp;Latest.USERID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;MC.Id&nbsp;=&nbsp;Latest.Max_Record

rodgerkong · Answer

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

Forum Discussion

Sub query

Share

Resources