select mc.type, max(mc.ratingtotal / mc.ratingnumbers) as rating,mt.name,mt.urlname, mc.id,mc.ratingtotal,mc.ratingnumbers,mc.location,mt.parenttype from codes mc 
			inner join types mt on mt.typeid=mc.type
where mt.parenttype=1
group by mc.type
order by mt.name desc