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 '13 at 16:43
  • I thought use DISTINCT and to get only the unique values of the field criteria, – Geog Jun 2 '13 at 19:39
  • For that have you tried Summary Statistics tool with a case field? – PolyGeo Jun 2 '13 at 21:44
  • in my case the count is changing dynamically so I cant use Summary Statistics tool, i need to use definition query. – Geog Jun 3 '13 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 '13 at 3:24

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.

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • 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 '13 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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