How to display duplicate (base on just multi cols)

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 2005 Forums

Transact-SQL (2005)

How to display duplicate (base on just multi cols)

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2010-01-22 : 14:11:27

Hello All,

Could you please explain me here in this tricky part.....

i have a table called "BlockInfo" with below columns

----> Name,City,State,Pin,

i woulf like to display duplicate rows
but the trick here is, i have to check if Name and City is Duplicated then the row is duplicated or if pin no is duplicated then whole record is assumed as duplicated

if just Name and City columns is Duplicated then the record is duplicated {OR} if pin no is duplicated then whole record is assumed as duplicated

how can i show (or write a query) to display all duplicated values (with satisfying above 2 conditions)

Please Help me
Thanks in advance
dhani

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-01-22 : 14:32:54

probably easiest to do it in two steps. for combining the steps into a single output maybe this:


select bi.*
from   BlockInfo bi
join   (
       select name, City from blockInfo group by name, city having count(*) > 1
       ) dup
              on dup.name = bi.name
              and dup.city = bi.city
UNION --ALL

select bi.*
from   BlockInfo bi
join   (
       select pin from blockInfo group by pin having count(*) > 1
       ) dup
              on dup.pin = bi.pin

Be One with the Optimizer
TG

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-22 : 14:38:43

Join 2 tables to your original table. One having [Name], [City], Count(*) called table 'A' and another having [PinNo], Count(*) called table 'B'

Then write in the conditions...

Where a.count > 2 or b.Count > 2

You are going to need to supply a unique Identifier for each row as well, So you may want to write the first table as a CTE..

With CTE (Name, City, State, Pin, RowID)
As
(
Select
*, Row_Number() over (order by Name, City, State, Pin)
From BlockInfo
)
Select...
Joins and things go here...
Conditions here...

NB: TG's Method is propably easier :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-23 : 07:49:09

[code]SELECT relevant columns....
FROM
(
SELECT *,
COUNT(PK) OVER (PARTITION BY Name,City) AS NameCityCnt,
COUNT(PK) OVER (PARTITION BY Pin) AS PinCnt,
FROM Table
)t
WHERE t.PinCnt > 1
OR t.NameCityCnt > 1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources