course_id course_name rollno description geog math1 course1 1 Default 40 2 course2 1 Default 502 course2 3 bbb 45 3 course3 1 abc 504 course4 1 Default 45 505 course5 1 abc 55 355 course5 2 aaa 45 35

course_id course_name rollno description geog math1 course1 1 Default 40 4 course4 1 Default 45 503 course3 1 abc 50 <- Since only one student, so it can be treated as default

course_id course_name rollno description geog math2 course2 1 Default 502 course2 3 bbb 45 5 course5 1 abc 55 355 course5 2 aaa 45 35

SELECT s.course_id, s.course_name, roll_no = MIN(s.roll_no), student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END, s.geog_marks, s.math_marksFROM #students sINNER JOIN ( SELECT course_id, geog_marks, math_marks, SetSize = COUNT(*) FROM #students GROUP BY course_id, geog_marks, math_marks) g ON g.course_id = s.course_id AND g.geog_marks = s.geog_marks AND g.math_marks = s.math_marksGROUP BY s.course_id, s.course_name, s.geog_marks, s.math_marks, g.SetSizeORDER BY s.course_id, roll_no

-- like this SELECT s.course_id, s.course_name, roll_no = MIN(s.roll_no), student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END, s.geog_marks, s.math_marksFROM #students sINNER JOIN ( SELECT course_id, geog_marks, math_marks, SetSize = COUNT(*) FROM #students GROUP BY course_id, geog_marks, math_marks) g ON g.course_id = s.course_id AND g.geog_marks = s.geog_marks AND g.math_marks = s.math_marksGROUP BY s.course_id, s.course_name, s.geog_marks, s.math_marks, g.SetSizeHAVING (CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END) = 'Default'ORDER BY s.course_id, roll_no-- or run the result set into a temporary table:IF object_id('TempDB..#Results') IS NOT NULL DROP TABLE #ResultsSELECT s.course_id, s.course_name, roll_no = MIN(s.roll_no), student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END, s.geog_marks, s.math_marksINTO #ResultsFROM #students sINNER JOIN ( SELECT course_id, geog_marks, math_marks, SetSize = COUNT(*) FROM #students GROUP BY course_id, geog_marks, math_marks) g ON g.course_id = s.course_id AND g.geog_marks = s.geog_marks AND g.math_marks = s.math_marksGROUP BY s.course_id, s.course_name, s.geog_marks, s.math_marks, g.SetSizeORDER BY s.course_id, roll_noSELECT * FROM #Results SELECT * FROM #Results WHERE student_name = 'Default'SELECT * FROM #Results WHERE student_name <> 'Default'