how to use MAX function in Definition Query/ArcMap?

1518
4
11-09-2018 12:49 PM
Highlighted
New Contributor III

Hi All,

How I can select a record in a column with Largest/MAX value in "Definition Query" in ArcMap?

for example, i have a feature with 10 records 

in the ID column; the records are 1, 2, 3,4,5,6,7,8,9,10

in the "Definition Query", i would like to select on the largest ID record 

i have tried all kinds of MAX sql but they didn't work, any ideas?

ID >= MAX(ID)

"ID" >= MAX("ID") ....etc

thanks.

WL 

Reply
0 Kudos
4 Replies
Highlighted
MVP Esteemed Contributor

The syntax would be

ID = (SELECT MAX(ID) FROM table)

Just a note of caution, using subqueries in definition queries isn't ideal.  Depending on the complexity of the data set and the subquery itself, there can be performance or portability issues.

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

This query selects the record with the maximum value... the 'select  … from … ' syntax

buried a bit in ...

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop 

Highlighted
Occasional Contributor

I am have a problem with the syntax  Hopefully someone can assist.  When i type in the sql expression Pro 2.5 finds an error but i can't seem to find what the errors is.  I even copied the script directly from the blog and only changed out the fields and table, but still no luck

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation 

GDP2006 > (SELECT MAX(GDP2005) FROM countries)

Not sure if or why but the query is on a stand alone table

Reply
0 Kudos
Highlighted
Occasional Contributor

Check the database / schema , and permissions to table / layer

(SELECT MAX(GDP2005) FROM gisdata.dbo.countries)

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

Reply
0 Kudos