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

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
Jim Horn

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

Jim Horn

>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

Jim Horn

< The above statement has been edited after original post >
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.

2016-02-09_11-53-26.jpg
ASKER CERTIFIED SOLUTION
awking00

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Horn

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.