SQL Query to find equal data

RyanGarnett — Sat, 18 Dec 2010 11:48:32 GMT

Hello,

Is there a way to find all the records is there are multiple records that have the same value? I have a dataset where I am trying to find records that are different in one field, but share a common value in a different field.

FID     STATUS     VALUE
0        New          100
1        New          101
2        Old           101
3        Old           102
4        New          102
5        New          102
6        Old           103

I was thinking something like:
Where ([STATUS] = 'New' OR [STATUS] = 'Old') AND ([VALUE] IS "the same")

I would expect to have records 1,2,3,4,5 returned and not 0 and 6. I am not sure what to write to run this type of query.

Thank you in advance.

Re: SQL Query to find equal data

DanaNolan — Mon, 20 Dec 2010 16:37:56 GMT

This sound like a COUNT problem. Within each group field, you want the singles.

Something like this, or a subquery (not sure if having is supported in ESRI, but subqueries sometimes are). If not, you can get a count using the Summary Statistics, and select from that table.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
GROUP BY department
HAVING COUNT(*) = 1;

topic SQL Query to find equal data in Data Management Questions

SQL Query to find equal data

Re: SQL Query to find equal data