Selecting max value in SQL query from Extract by Attributes?

Question

I currently have a raster that has 3 columns in the attribute table: OBJECTID, Value, and Count. As part of ModelBuilder, I am running this raster through the Extract by Attributes tool. The Extract by Attributes tool requires an SQL Where clause. I can't seem to figure out how to write something that will simply select the highest/maximum Count value.

I have tried MAX(Count), SELECT MAX(Count), "MAX(Count)", and many others. I checked the documentation page tool, but didn't find anything that helped.

The error I am getting most of the time:

enter image description here

• Mapperz: SELECT MAX(TABLE_COLUMN) from SCHEMA.TABLENAME (works on Oracle)
• Jared: Can you please elaborate? How do I find the schema/tablename? Is it not the same as the raster name?
• Mapperz: for your raster maximum value can be found using 'Cell Statistics' gis.stackexchange.com/questions/65747/…
• Jared: @Mapperz How can I use the output from Cell Statistics as an input for the Extract by Attributes tool?

Answer 1

This is the model that you are seeking:

Model

You create the table view of the raster, sort that into an in-memory table, then use the Get field value which only every looks at the first row which is your sorted data by count and then the extract by attribute is an SQL clause Count = %MAX_Value%.

• Jared: Thank you very much! I'm surprised it requires so many steps, and I never would have thought to do this on my own.
• Hornbydd: I agree it does seem an unnecessary number of steps but even if you coded this in python you would still need to loop the entire table to identify the maximum value in the count field.