I am trying to write a query to get distinct users with a max (entity_ID) value
For example I have the following user:
section_id user_id role status Entity_ID
584186.0.02 137545 student Active 001
585033.0.02 137545 student Active 002
I need the second record as the entity-ID is the max value. I have to have all the fields with the exception of the entity_ID. How do I add a where clause to only pull the user data with the max entity_ID value?
SELECT distinct SA.Course_ID + '.0.' + SR.SectionNumber as 'section_id', SR.StudentInternalID AS 'user_id', 'student' AS 'role', SR.ClassStatus AS 'status', SA.Entity_IDFROM ZZ_TEST_SISRostersSW AS SR INNER JOIN ZZ_TEST_SIS_AllCourses AS SA ON SR.Course = SA.Course_Number AND SR.Entity = SA.Entity_ID AND SR.SectionNumber = SA.sky_SectWHERE (SR.ClassStatus = 'Active') AND SA.building = '011' and InProgress = 'yes' and (SR.Course = 'X26510')order by SR.StudentInternalID
Open in new window