Comparing fields within a select

Hi there,

Is it possible for a query to return all the records in a table which have the same value in a given column?

SELECT * FROM table1 WHERE field1 = 2

..would return all the records from table1 which have a field1 value of 2 but I don't want to specify the value - just that all the records have the same value.

Cheers,

WT.I'm not sure I understand what you're trying to do. Are you trying to return all records where the values in field1 are repeated? Can you provide an example to clarify?|||I think I understand what you are asking for. You're looking for all records where there are duplicates in field1.
Kind of the opposite of a DISTINCT query.
If you do a self join you can get what you want.
Here's the generic form...
SELECT * FROM table1 WHERE field1 IN (SELECT DISTINCT T1.field1 FROM table1.T1, table1.T2 WHERE T1.field1=T2.field1 AND T1.Field2<>T2.field2)

Here's an example using the Customers table from NorthWind that will return all the customers in cities where there is more than one customer in that city...
SELECT DISTINCT C1.CITY FROM Customers C1, Customers C2 WHERE C1.City = C2.City and C1.CustomerID <> C2.CustomerID|||An easier way to do this (if I understand your question):

SELECT CompanyID, COUNT(*)
Companies
GROUP BY CompanyID
HAVING COUNT(*) > 1