SELECT areasymbol AS Area_symbol, musym AS Map_unit_symbol, muname AS Map_unit_name, mu.mukey/1 AS MUKEY,
(SELECT TOP 1 interphrc
FROM mapunit AS m1
INNER JOIN component AS c1 ON c1.mukey=m1.mukey
INNER JOIN cointerp AS ci1 ON c1.cokey = ci1.cokey AND m1.mukey = mu.mukey AND ruledepth = 0 AND mrulename LIKE
'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'
GROUP BY interphrc, comppct_r
ORDER BY SUM(comppct_r) over(PARTITION BY interphrc) DESC) AS Dominant_class,
(SELECT TOP 1 ROUND (AVG(interphr) over(PARTITION BY interphrc),2)
FROM mapunit AS m2
INNER JOIN component AS c2 ON c2.mukey=m2.mukey
INNER JOIN cointerp AS ci2 ON c2.cokey = ci2.cokey AND m2.mukey = mu.mukey AND ruledepth = 0 AND mrulename LIKE
'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'
GROUP BY interphrc, interphr
ORDER BY SUM (comppct_r) DESC) AS Dominant_rating
FROM legend AS l
INNER JOIN mapunit AS mu ON mu.lkey = l.lkey AND mukind = 'complex' AND l.areasymbol LIKE
'MO123'
INNER JOIN component AS c ON c.mukey = mu.mukey
INNER JOIN cointerp AS ci ON c.cokey = ci.cokey AND ruledepth = 0
AND mrulename LIKE 'NCCPI - National Commodity Crop Productivity Index (Ver 2.0)'
ORDER BY areasymbol, musym, muname, mu.mukey