January 21st, 2012, 12:50 PM #1
SQL query that will show which fields are different when another field has duplicates
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:
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.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By austin2359 in forum Linux and UnixReplies: 1Last Post: March 14th, 2011, 10:24 AM
By Tekk in forum Webmastering and ProgrammingReplies: 2Last Post: September 24th, 2004, 12:01 PM
By Tekk in forum Webmastering and ProgrammingReplies: 8Last Post: November 6th, 2003, 11:26 AM
By sixf00t4 in forum Webmastering and ProgrammingReplies: 2Last Post: April 29th, 2003, 03:57 PM