Thursday, March 22, 2012

Distinct in Common Table Expressions CTE

I have managed to write my first CTE SQL that handles recursion but I have a problem with distinct - can't get that to work!!

My CTE:

WITH StudentsHierarchy(SystemID1, GroupID, AccessType, AccessGroupID, StudentID, HierarchyLevel) AS(--Base CaseSELECT SystemID,GroupID,AccessType,AccessGroupID,StudentID,1 as HierarchyLevelFROM UserGroup aUNION ALL--Recursive stepSELECT u.SystemID,u.GroupID,u.AccessType,u.AccessGroupID,u.StudentID,uh.HierarchyLevel + 1 as HierarchyLevelFROM UserGroup uINNER JOIN StudentsHierarchy uh ONu.GroupID = uh.AccessGroupID)Select sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student swhere sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy shWHERE AccessType = 'S'

and I would like to have a distinct on the StudentID like:

Select DISTINCT sh.StudentID, sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student swhere sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy shWHERE AccessType = 'S'

How should I do?

is it possible to bump this?sql

No comments:

Post a Comment