I am able to get 1st and 2nd output from below query :

For 1st output :

select

a.course_id

,a.[Course]

,a.[Rollno]

,a.Description

,a.[Geog Marks]

,a.[Math Marks]

from

(

select

course_id

,min(course_name) as 'Course'

,min(roll_no) as 'Rollno'

,case when count(course_id)>1 then min('Default') else min(student_name) end as Description

,geog_marks as 'Geog Marks'

,math_marks as 'Math Marks'

from students

group by course_id,geog_marks,math_marks

) a

group by

a.course_id

,a.[Course]

,a.[Rollno]

,a.Description

,a.[Geog Marks]

,a.[Math Marks]

and for 2nd output :

select

a.course_id

,min(a.course) as 'Course'

,min(a.rollno) as 'Rollno'

,min(a.description) as 'Description'

,min(a.[Geog Marks]) as 'Geog Marks'

,min(a.[Math Marks]) as 'Math Marks'

from

(

select

course_id

,min(course_name) as 'Course'

,min(roll_no) as 'Rollno'

,case when count(course_id)>1 then min('Default') else min(student_name) end as Description

,min(student_name) as 'Student Name'

,min(geog_marks) as 'Geog Marks'

,min(math_marks) as 'Math Marks'

from students

group by course_id,geog_marks,math_marks

) a

group by a.course_id

having count(a.course_id)=1

For 3rd,

but I am not able to fetch only course 2 and 5's rows from 1st query. Kindly help me how do I get 3rd output.

Regards

Girish Sharma