Select to view content in your preferred language

Definition query to remove duplicate field values?

2308
5
05-12-2022 08:40 AM
David_Brooks
MVP Regular Contributor

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?


David
..Maps with no limits..
0 Kudos
5 Replies
jcarlson
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS
curtvprice
MVP Esteemed Contributor

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.

0 Kudos
jcarlson
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS
0 Kudos
curtvprice
MVP Esteemed Contributor

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.