SELECT SUM(mo.areaovacres*comppct_r/100) over (partition by compname,lo.areasymbol) as total_acres, compname,
lo.areasymbol, m.musym, m.muname, m.muacres, mo.areaovacres
INTO #T1
FROM legend AS l
INNER JOIN laoverlap AS lo ON lo.lkey=l.lkey and l.areasymbol LIKE
'OH%'
INNER JOIN muaoverlap AS mo ON mo.lareaovkey = lo.lareaovkey
INNER JOIN component AS c ON c.mukey=mo.mukey and majcompflag='yes'
INNER JOIN mapunit AS m ON m.mukey=c.mukey and m.mukey=mo.mukey
where lo.areatypename ='mlra'
select distinct compname, total_acres, areasymbol
INTO #T2
from #T1
order by total_acres desc, areasymbol
select compname, total_acres, areasymbol,
RANK() over (partition by areasymbol order by total_acres desc) as rank_area
INTO #T3
from #T2
select compname AS Component_name, total_acres AS Total_acres, areasymbol AS MLRA_symbol, rank_area AS Ranking_by_area
FROM #T3
Where rank_area <6
DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3