>> have the following table in my database: <<
Where is the DDL for this table? What are the keys? What data types? What are the constraints? You really posted nothing. But worse than that, you seem to believe that there is such a thing as a generic, magical, universal "id" in RDBMS. There is not. We use keys and by definition, a key is a subset of the columns of a table such that they are unique for every row in that table. Guessing at what you might have meant to post, I would guess this:
CREATE TABLE Insurance_Policies
(foobar_id CHAR(2) NOT NULL PRIMARY KEY
CHECK(foobar_id LIKE '[0-9][0-9]'),
policy_nbr CHAR(5) NOT NULL
CHECK (policy_nbr LIKE '[0-9][0-9][0-9][0-9][0-9]')
INSERT INTO Insurance_Policies
I am looking for a sql query that will compare the policy_nbr column values in all 5 rows and return those rows which have a value equal to at least one other row.
SELECT policy_nbr, MIN(foobar_id), MAX(foobar_id)
GROUP BY policy_nbr
HAVING COUNT(*) > 1;
I've made an assumption, since we don't have any specs, that there are only two duplicate foobar_id per policy number. This is one of the many many reasons that we ask people to post DDL. If I was correct you would've put constraints on your table to assure this.
Please post DDL and follow ANSI/ISO standards when asking for help.