Using MAX in SQL Expression of ArcMap ModelBuilder

Question

I am trying to use a SQL expression in order to select values that are >= 25% of its max value in that column. For instance, VALUE >= MAX(VALUE)*0.25, and those will ultimately be the selected points it pulls out.

Is there syntax to make that happen, or am I going to need to create a new column and use field calculator on it?

Biggest issue with that is I will have to do this for about 250 files. I am also trying to create this in ModelBuilder.

When I try it as above it just returns that there is an error with the expression.

Answer 1

The technique I use to do this in ModelBuilder is to:

1. Run Summary Statistics (Analysis) to write the MAX value into its output table
2. Use Get Field Value (ModelBuilder) to read the first row of the table just created which holds that MAX value

Answer 2

I think the problem you are having is, with your expression as is, this condition you wrote is being evaluated for each row/record in your data. So, if you had a table like:

OID | Value
 1  | 123
 2  | 456
 3  | 789

my understanding is that your expression, with real values substituted in for variables, would look like:

123 >= MAX(123)*0.25
456 >= MAX(456)*0.25
789 >= MAX(789)*0.25

First off, you need to make sure your value field is of a numeric field type (long/short integer, double, float). Then, if that is true, you need to look at the help concept at http://resources.arcgis.com/en/help/main/10.2/index.html#//00s500000033000000 and scroll down to the section on subqueries and read through that as that I think is what you are really looking for. I don't know your table names and such, so I don't know the exact query that would be needed, but it might look something like

VALUE >= (SELECT MAX(VALUE)*0.25 FROM TABLE)

That should hopefully get you at least in the ball park. If you do a subquery, it is going to evaluate each record (the first VALUE variable), against the results of the subquery( which can look at the entire table and extract a MAX value). To my knowledge, with the way ArcGIS Desktop SQL input options are set up, you can generally only use the functions like AVG, COUNT, MIN, MAX, and SUM in subqueries.