topic Re: Definition query to remove duplicate field values? in ArcGIS Pro Questions

Definition query to remove duplicate field values?

David_Brooks — Thu, 12 May 2022 15:40:13 GMT

Is there a way of adding a definition query to a layer, to remove features that have a duplicate field value in the table?

I routinely have an FC with a table added to the layout, where some feature attributes are identical and don't need to be visible in the table. I don't want to delete the feature, but I also don't want duplicate rows in the table, so could point the table to a version of the layer with duplicates queried out, but don't know how to do this. Any ideas?

Re: Definition query to remove duplicate field values?

jcarlson — Thu, 12 May 2022 15:53:37 GMT

If I'm not mistaken, definition queries are evaluated on a per-feature basis, so I don't know that you could write a query that could look at other rows. Do you need to edit the table? You could run summary statistics on the fields you want in the layout table, which would group everything by the unique combinations, then just don't display the output statistic field, leaving you with one row for each unique input row.

Re: Definition query to remove duplicate field values?

curtvprice — Thu, 12 May 2022 16:25:50 GMT

The tools Find Identical and Remove Identical may be helpful to you.

Another approach to consider is the Make Query Layer tool, which supports SQL operators like DISTINCT - but this requires your feature class to be of certain supported data formats.

Re: Definition query to remove duplicate field values?

jcarlson — Thu, 12 May 2022 16:38:05 GMT

Oh, a good query layer is always a nice solution. Can you use Remove Identical without modifying the input layer? That sounds like it could be a good route, too.

Re: Definition query to remove duplicate field values?

curtvprice — Thu, 12 May 2022 18:38:35 GMT

Nope, Remove Identical deletes records -- I always run it on a copy, just in case...

Re: Definition query to remove duplicate field values?

JoshuaBixby — Fri, 13 May 2022 15:59:15 GMT

Depending on the back-end DBMS, you can do subqueries in definition queries that would be able to do what the OP asks. The issue is it would be a performance killer, and would make the layer slow for anything but the smallest of data sets.