Select duplicates with different second field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select duplicates with different second field

Author

Topic

jlandwehr
Starting Member

23 Posts

Posted - 2008-03-17 : 17:05:11

A simple question.

I am trying to select for all duplicates, that have a different second field. (i.e. All TAXKEY's that are duplicates should have the same MAPID. If they don't, these are the ones I need to see.)

Thanks,

Jim

modi_sanjay
Starting Member

9 Posts

Posted - 2008-03-17 : 18:01:21

Check this out Replace col1 and col2 and table1 as needed.

SELECT Col1, Col2, count(*)
FROM Table1
WHERE Col1 IN (
SELECT Col1
FROM Table1
GROUP BY Col1
HAVING count(Col1) > 1)
GROUP BY Col2, Col1

jlandwehr
Starting Member

23 Posts

Posted - 2008-03-19 : 09:35:07

Modi,

Thanks for your response. However the query you posted is still not filtering out the records with "different" Col2 values. The results it gave me had all duplicate TAXKEY's, including those that are "valid" (i.e. that the MAPID is the same).

I am looking for something that finds all duplicate TAXKEYS where it tests against the MAPID column, and if the MAPID is different for the same TAXKEY, these are what I want to see.

Thanks again and I appreciate any additional help.

Best regards,

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 09:41:26

How about this?
SELECT TAXKEY
FROM Table
GROUP BY TAXKEY
HAVING COUNT(DISTINCT MAPID)>1

jlandwehr
Starting Member

23 Posts

Posted - 2008-03-19 : 09:59:54

Viskah,

That's it! It worked perfectly.

Thanks so much for your quick and helpful reply.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 10:33:05

You're welcome.