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
For starters, let's clean up your code with proper indenting, eliminating unnecessary spaces and tabs, and consistent use of capitalization so that it doesn't look like a spaghetti pile.
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 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
>I need the second record as the entity-ID is the max value.
Ok. There are more than a couple of ways to pull this off, but a a quick and dirty way would be to throw the above query into a CTE, with a counter value for the ID, and then just pick the highest counter.
;WITH cte as ( 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_ID, ROW_NUMBER () OVER (PARTITION BY SR.StudentInternalID ORDER BY Entity_ID DESC) as rank_order FROM ZZ_TEST_SISRostersSW AS SR JOIN ZZ_TEST_SIS_AllCourses AS SA ON SR.Course = SA.Course_Number AND SR.Entity = SA.Entity_ID AND SR.SectionNumber = SA.sky_Sect WHERE SR.ClassStatus = 'Active' AND SA.building = '011' AND InProgress = 'yes' AND SR.Course = 'X26510')SELECT section_id, user_id, role, status, Entity_IDFROM cteWHERE rank_order = 1ORDER BY user_id
Thank you for the suggestion but I do not think this will work. Let me try to explain a little better what I am trying to accomplish. I have the fields section_id, user_id, role, status and Entity_ID.
The section_id will be different values dependent upon the Entity_ID. What I would like to do is only get the user where the entity value is the greatest. I do not want to see the other record for the user
In the image below, I only want to see the highlighted users.
Fixed a typo in the above, had PARTITION BY user_id (the alias) instead of thee actual value SR.StudentInternalID, otherwise this should have been a complete solution.
Open in new window