SQL Query - MAX and GROUP

Discussion created by daniels1975 on Oct 5, 2010
Latest reply on Oct 6, 2010 by daniels1975
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