Hello Vaibhav,
Adding a recursion will be one way..
i tried to create a solution which will avoid recursion as below
SELECT DISTINCT A.* from Demo A LEFT JOIN DEMO B ON A.GroupID != B.ParentGroupId
WHERE ISNULL(A.ParentGroupId,-99) != 6 AND ISNULL(A.ParentGroupId,-99) NOT IN ( SELECT DISTINCT A.GroupID from Demo A LEFT JOIN DEMO B ON A.GroupID = B.ParentGroupId WHERE B.ParentGroupId !=6)
Hope that it should solve your problem(i know it looks weird but for sake of avoiding recursion it can be used)
Please mark it as answer if this seems correct to you