SQL subquery with max

mkamp81
mkamp81 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
< The above statement has been edited after original post >
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Information Technology Specialist
Commented:
SELECT section_id, user_id, role, status, entity_id from
(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 SA.Entity_ID DESC) rn
 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')
 ) x
where rn = 1
order by user_id;
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial