Sub query
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.IdASModID ,MC.TYPEASResID ,CAST(MC.EndDateTimeASDATETIME2)ASEndDate ,CAST(MC.ExpiryDateASDATETIME2)ExpiryDate ,CASE WHENExpiryDate>=GETDATE()AND IsDeleted=0THEN1 ELSE0 ENDASCompliance FROM MC LEFTJOINOG ONMC.USERID=OG.USERID INNERJOIN ( SELECT MC.USERID ,MAX(MC.Id)ASMax_Record FROMMC GROUPBYMC.USERID)Latest ONMC.USERID=Latest.USERID ANDMC.Id=Latest.Max_Record
How to create category based on 1 column
Hi all, I am new to SQL and I am finding a way to categorise 1 column in my table. Currently I have 1 column called ID and I want to add 1 column that determine the category of that ID. My desired out is as following: ID Category 1 a 2 b 3 a 4 a 5 b The thing is, for 1 category alone, I have like 50+ ID involved, and I have 20+ categories. How do I create a query to simplify this? Many thanks.
