duplicates - 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 2000 Forums

SQL Server Development (2000)

duplicates

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-11 : 02:14:53

I have the following table:

CREATE TABLE t1
(id int PRIMARY KEY,
descr varchar(50)
)

sometimes there are duplicate descrs for different ids like this

INSERT INTO t1
SELECT 1, 'Desc1' UNION ALL
SELECT 2, 'Desc2' UNION ALL
SELECT 3, 'Desc1' UNION ALL
SELECT 4, 'Desc4' UNION ALL
SELECT 5, 'Desc2' UNION ALL
SELECT 6, 'Desc6' UNION ALL
SELECT 7, 'Desc2'

i want to query those duplicated and i tried this

SELECT a.id, MAX(b.id) id2, a.descr
FROM t1 a
INNER JOIN t1 b
ON a.descr = b.descr AND a.id <> b.id
GROUP BY a.id, a.descr
ORDER BY a.descr

the result is:

id id2 descr
----- ----- -----
1 3 Desc1
3 1 Desc1
5 7 Desc2
7 5 Desc2
2 7 Desc2

but what i want is

id id2 descr
----- ----- -----
1 3 Desc1
2 5 Desc2
2 7 Desc2

how can i get this result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-11 : 03:04:32

what happens when you've only one occurance of same description? dont you want to list them at all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-11 : 03:17:44

if you want to list the records with only one occurance od descr also,use


SELECT tmp.Start,t1.id,t1.descr
FROM t1
INNER JOIN (SELECT descr,COUNT(id) AS [Count],MIN(id) AS Start
FROM t1
GROUP BY descr)tmp
ON t1.descr=tmp.descr
WHERE tmp.[Count]=1
OR tmp.Start <> t1.id

else use


SELECT tmp.Start,t1.id,t1.descr
FROM t1
INNER JOIN (SELECT descr,COUNT(id) AS [Count],MIN(id) AS Start
FROM t1
GROUP BY descr)tmp
ON t1.descr=tmp.descr
WHERE  tmp.Start <> t1.id

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-11 : 03:26:17

Thank you visakh. i enjoyed your solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-11 : 03:36:09

welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-11 : 23:05:30

select t2.id2,t.id,t.descr from t1 as t
cross apply ( select descr,count(id)as id1,min(id) as id2 from t1 group by descr) as t2
where t2.id2 <> t.id AND t.descr = t2.descr

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 23:09:31

quote:
Originally posted by Nageswar9

select t2.id2,t.id,t.descr from t1 as t
cross apply ( select descr,count(id)as id1,min(id) as id2 from t1 group by descr) as t2
where t2.id2 <> t.id AND t.descr = t2.descr

Cross apply doesn't work in SQL 2000.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-11 : 23:13:37

Oh Sorry, Ok Sodeep

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-12 : 01:10:52

If u also want to list the records with one occurance

declare @table table(id int,descrip varchar(99))

INSERT INTO @table
SELECT 1, 'Desc1' UNION ALL
SELECT 2, 'Desc2' UNION ALL
SELECT 3, 'Desc1' UNION ALL
SELECT 4, 'Desc4' UNION ALL
SELECT 5, 'Desc2' UNION ALL
SELECT 6, 'Desc6' UNION ALL
SELECT 7, 'Desc2' UNION ALL
SELECT 4, 'Desc6' UNION ALL
SELECT 8, 'Desc6' UNION ALL
select 1, 'Desc8'

select t1.id,case when t1.cnt = 1 then null else t.id end as id,t1.descrip from @table t
inner join (select min(id) as id,count(id) as cnt ,descrip from @table group by descrip) t1 on (t.descrip = t1.descrip) where t.id <> t1.id or cnt=1

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources