I have two fields in a table, customer and reference.
A problem has occured that means the same references have been assigned to different customers in errors.
Ignoring the fact that constraints could have prevented this, how would I identify the rows where more than one customer is attached to the same reference.

The current SQL I am using is:

select customer,reference
from table
where reference in
(select reference from
(select reference,count(*) from
table
group by reference
having count(*) > 1))

This appears to work but I suspect there is a simpler method - any ideas as this can take a long time?

You don't need so many subqueries:

select customer,reference
from table
where reference in
(select reference from table
group by reference
having count(*) > 1)

I don't think there is a quicker way than that, assuming there is an index on table.reference (if not add maybe one first).