Hi, if you remember your last question with three Choices for preferring for each students,i rated for most preferred students and give them rate, and the sql code is below , you need to continue and set down the students in their group,in fact groups are found base on most preferred students , you only need set the students in their group they said prefer:
<pre>
if OBJECT_ID(N'StudentCoreRate','U') !=0
drop table StudentCoreRate
create table StudentCoreRate (StudentID int, Rate decimal(10,2))
select FirstPrefer.Prefere1 as sid, Count(FirstPrefer.Prefere1) FirstPrefer into #FirstPrefer
from Preference FirstPrefer
group by FirstPrefer.Prefere1
insert into StudentCoreRate select Students.ID , FirstPrefer
from Students
inner join #FirstPrefer on Students.ID = #FirstPrefer.SID
where FirstPrefer > 1
select SecondPrefer.Prefere2 sid, Count(SecondPrefer.Prefere2) SecondPrefer into #SecondPrefer
from Preference SecondPrefer
group by SecondPrefer.Prefere2
update StudentCoreRate set Rate = Rate + cast(SecondPrefer as decimal)/2
from #SecondPrefer
inner join StudentCoreRate on StudentCoreRate.StudentID = #SecondPrefer.SID
where SecondPrefer > 1
insert into StudentCoreRate select Students.ID , cast(#SecondPrefer.SecondPrefer as decimal)/2
from Students
inner join #SecondPrefer on Students.ID = #SecondPrefer.SID
left join StudentCoreRate on StudentCoreRate.StudentID = #SecondPrefer.SID
where StudentCoreRate.StudentID is null
and #SecondPrefer.SecondPrefer > 1
select ThirdPrefer.Prefere3 sid, Count(ThirdPrefer.Prefere3) ThirdPrefer into #ThirdPrefer
from Preference ThirdPrefer
group by ThirdPrefer.Prefere3
update StudentCoreRate set Rate = Rate + cast(ThirdPrefer as decimal)/3
from #ThirdPrefer
inner join StudentCoreRate on StudentCoreRate.StudentID = #ThirdPrefer.SID
where ThirdPrefer > 1
insert into StudentCoreRate select Students.ID , cast(#ThirdPrefer.ThirdPrefer as decimal)/3
from Students
inner join #ThirdPrefer on Students.ID = #ThirdPrefer.SID
left join StudentCoreRate on StudentCoreRate.StudentID = #ThirdPrefer.SID
where StudentCoreRate.StudentID is null
and ThirdPrefer > 1
select * from StudentCoreRate
order by rate desc
select * from #SecondPrefer
select * from #ThirdPrefer
select * from Preference
drop table #FirstPrefer
drop table #SecondPrefer
drop table #ThirdPrefer