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