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

mkamp81Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
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 DudeCommented:
>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 DudeCommented:
< The above statement has been edited after original post >
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
mkamp81Author 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
awking00Information Technology SpecialistCommented:
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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornSQL Server Data DudeCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.