troubleshooting Question

SQL subquery with max

Avatar of mkamp81
mkamp81 asked on
Microsoft SQL ServerSQL
6 Comments1 Solution106 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
awking00
Information Technology Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros