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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
>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
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.
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.
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;
(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;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial