Is there a way to filter unique values only from a column in ArcMap using definition query? i.e to use a Definition Query to filter out just those values which occur only once.

In a Personal Geodatabase I can use "DISTINCT", what about File Geodatabase format?

Thanks for your help.

  • 2
    Not sure what you are asking here? A definition Query filters a layer based upon a criteria (e.g. ID = 10 or type="A"). If you want to find the unique values you need to traverse the table completely before you can answer what is unique. What happens when you find more than one row with the same value, which do you filter? I don't think a definition query is meant for this. If you want to find to find the unique values in a field then use the summary tool and set the field of interest to be the case field.
    – Hornbydd
    Jun 2, 2013 at 16:43
  • I thought use DISTINCT and to get only the unique values of the field criteria, Jun 2, 2013 at 19:39
  • For that have you tried Summary Statistics tool with a case field?
    – PolyGeo
    Jun 2, 2013 at 21:44
  • in my case the count is changing dynamically so I cant use Summary Statistics tool, i need to use definition query. Jun 3, 2013 at 6:16
  • 1
    Perhaps you can edit your question to more accurately describe what you are after. I think you are wanting to use a Definition Query to filter out just those values which occur only once. In any event I think your best bet may be an enhancement request via ArcGIS Ideas.
    – PolyGeo
    Jul 17, 2013 at 3:24

2 Answers 2


I'm not sure your end goal. Definition Query is there to remove items from the layer - NOT to group items within a layer. You will want to use symbology to group items in a layer. However, here is a sample definition query that will select out of your layer ONLY the records with the first instance of each value: (replace items in brackets with your own field and layer names).

[primarykey] in (
    select min([primarykey]) from [Your layer name] group by [value]

For ArcGIS, you will need to use 'summary statistics'. That will give you a list of all the uniques, and a count of how many times each occurrs.

If you want to select for all the records which occurr only once: Join the output table from the summary statistics table to the file, using the field you summarized them on as the join.

Add a definition query that says "Count=1". (If you are working with geodatabases, instead put "Count=1 & IS NOT NULL".)

Variables that are unique will have a count of 1. Variables that show up more than once will have a count greater than 1.

  • Can you elaborate on these steps, please? If you simply add a field (which puts in all nulls) and then use that definition query I think you will either get all records or an error - and not the desired result.
    – PolyGeo
    Nov 4, 2013 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.