Data repeatation - SQL Server Forums

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)

Data repeatation

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-09-21 : 03:43:54

name age empcpde mobno
A 1 1 111
A 1 1 112
A 2 1 111
B 2 1 111
B 1 1 121
B 1 1 111
C 1 1 112
D 1 1 112
D 2 2 111

this is the table above .

i want to achive 2 task:
1) result set where name ,age and empcode are same for more then once
2) where all the columns r same more then once

these is a dumy table but in actual table this situation
is posible .. hope u ll get the scenario.

scoo

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-21 : 04:03:57

Is this you are looking for ?

Select name,age,empcode,count(*) as NoOfRepeatation
from yourtable
group by name,age,empcode
having count(*) >1

Select name,age,empcode,mobno
from yourtable
group by name,age,empcode,mobno
having count(*) >1

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-09-21 : 05:31:23

yes this is ok but i want those data which is same Eg:
name and age and emp three same ... this three combine to make a unique data , and i want those data which is duplicate to combination of this combine three fields.
Eg:
name1 10 111
name1 10 111

scoo

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-21 : 06:47:31

DECLARE @T TABLE (name varchar(10),age int,empcode int,mobno int)

INSERT @t VALUES('a',1,1,111)
INSERT @t VALUES('a',1,1,112)
INSERT @t VALUES('a',2,1,111)
INSERT @t VALUES('b',2,1,111)
INSERT @t VALUES('b',1,1,121)
INSERT @t VALUES('b',1,1,111)
INSERT @t VALUES('c',1,1,112)
INSERT @t VALUES('d',1,1,112)
INSERT @t VALUES('d',2,2,111)
select * from @t

select name,age,empcode, COUNT(*) [Count] from @t
group by name,age,empcode
having count(*)>1

select name,age,empcode,mobno, COUNT(*) [Count] from @t
group by name,age,empcode,mobno
having count(*)>1

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-21 : 07:39:46

Try this -
I hope that I understood the requirement correctly.


DECLARE @T TABLE (name varchar(10),age int,empcode int,mobno int)

INSERT @t VALUES('a',1,1,111)
INSERT @t VALUES('a',1,1,112)
INSERT @t VALUES('a',2,1,111)
INSERT @t VALUES('b',2,1,111)
INSERT @t VALUES('b',1,1,121)
INSERT @t VALUES('b',1,1,111)
INSERT @t VALUES('c',1,1,112)
INSERT @t VALUES('d',1,1,112)
INSERT @t VALUES('d',2,2,111)
select * from @t

;WITH CTE AS 
( 
SELECT Row_Number() OVER ( Partition BY Name, Age, EmpCode ORDER BY (SELECT Name) ) RowNo, Name, Age, empCode, mobNo FROM @t 
)

SELECT A.Name, A.Age, A.EmpCode, A.MobNo FROM 
CTE A
INNER JOIN 
(
SELECT Name, Age, EmpCode, SUM(RowNo) SumRow FROM CTE GROUP BY Name, Age, EmpCode Having Sum(RowNo) > 1
) B ON A.Name = B.Name AND A.Age = B.Age AND A.EmpCode = B.EmpCode

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Sachin.Nand

2937 Posts

Posted - 2010-09-21 : 07:59:23

Maybe this


DECLARE  @T  TABLE( 
                   name    VARCHAR(10), 
                   age     INT, 
                   empcode INT, 
                   mobno   INT 
                   ) 

INSERT @t VALUES('a',1,1,111) 

INSERT @t VALUES('a',1,1,111) 

INSERT @t VALUES('a',1,1,111) 

INSERT @t VALUES('a',1,1,112) 

INSERT @t VALUES('a',1,1,112) 

INSERT @t VALUES('a',2,1,111) 

INSERT @t VALUES('b',2,1,111) 

INSERT @t VALUES('b',1,1,121) 

INSERT @t VALUES('b',1,1,111) 

INSERT @t VALUES('c',1,1,112) 

INSERT @t VALUES('d',1,1,112) 

INSERT @t VALUES('d',2,2,111) 

SELECT * FROM   @t 

SELECT   name, 
         age, 
         empcode, 
         mobno, 
         Count(rowid) [noof duplicates] 
FROM     (SELECT *, 
                 Dense_rank() 
                   OVER(ORDER BY name, age, empcode, mobno) rowid 
          FROM   @T) t 
GROUP BY name, 
         age, 
         empcode, 
         mobno, 
         rowid 
HAVING   Count(CASE 
                 WHEN rowid = 1 THEN 1 
                 ELSE 0 
               END) >= 2

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources