how to use MAX function in Definition Query/ArcMap?

WilsonLee

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 

arcmap.png

thozub_dal_2-1653603420975.png

JoshuaBixby

The syntax would be

ID <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="token function">MAX</SPAN><SPAN class="punctuation token">(</SPAN>ID<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">FROM</SPAN> <SPAN class="keyword token">table</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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.