Link to home
Create AccountLog in
Avatar of mkamp81
mkamp81

asked on

SQL subquery with max

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_ID

FROM         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_Sect

WHERE 	
(SR.ClassStatus = 'Active') AND SA.building = '011' and InProgress = 'yes' and (SR.Course = 'X26510')

order by SR.StudentInternalID

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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_ID
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'
ORDER BY SR.StudentInternalID

Open in new window

>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_ID
FROM cte
WHERE rank_order = 1
ORDER BY user_id

Open in new window

< The above statement has been edited after original post >
Avatar of mkamp81
mkamp81

ASKER

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.

User generated image
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.