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?
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
Microsoft SQL ServerSQL
Last Comment
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.
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
< The above statement has been edited after original post >
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]()
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.
ASKER CERTIFIED SOLUTION
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.
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.
Open in new window