Hey everyone,

I'm fairly new to complex SQL and I'm trying to figure out an SQL query for work.

The problem is, we have data in a single table where Field1 has duplicates and Field2 will have unique values for the duplicate field1.

What I need to do is show the value of Field1 when there are duplicates of it AND the value of Field2 is unique for those duplicates.

Here's some sample data:

Field1 Field2
123456789 1234
123456789 7890
456789012 4567
234567890 8765
234567890 8765
765432109 0987

What I need is to show the value of Field 1, when it is duplicated but the value of Field2 is different. So in the sample data above, it would return "123456789" only because it is duplicated and Field2 is unique for each of those records.

But it shouldn't return the "234567890" record because Field2 is the same for each of the records.

The table is well over a million records so I need something that won't kill system performance, this is a realtime transaction processing system. The database is DB/2 but it will also need to work on an MS SQL Server database too.

I've created this SQL, but all it does is return all records that have Field1 as a duplicate, regardless of the value of Field2:

SELECT FIELD1,COUNT(*) FROM OFFSETS GROUP BY FIELD1 HAVING (COUNT(*) > 1) WITH UR

Any help would be greatly appreciated.

Sean