SQL Query to find equal data

[RyanGarnett]

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.

[DanaNolan]

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;