SQL Query - MAX and GROUP

Discussion created by daniels1975 on Oct 5, 2010
I'm working with some SSURGO soils data and I would like to select the soil component that has the highest percentage for each individual mukey. Most soils have one component and so that's 100% (easy enough selection), but some have multiple components. However, for those I would simply like to extract the rows of data that pertain to the component that has the highest percentage. I've attached a screen shot that shows one example of this (see OBJECTID's 39 & 40). In this case I would like to select out the Thurman component, along with all those soils that have only one component, and export them to a new table. I've tried the following SQL expression, but it doesn't seem to work. Any help would be greatly appreciated.

[comppct_r] in (SELECT MAX ([comppct_r]) FROM ne_cultivated GROUP BY [mukey] )

The field mukey is hidden in my table image, but for each component it is the same

Rob Daniels
Operations Manager
Department of Geography
Kansas State University