Select and Compare duplicate columns

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 and Compare duplicate columns

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-02 : 06:24:00

hello,
how do i check to compare two columns to show duplicate fields. And how many times they appear

And to list them in seperate rows
thanks
Afrika

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-02 : 06:30:38

select tbl.col1,tbl.col2,tbl.*
from tbl
join (select col1,col2 from tbl group by col1,col2) as dups
on tbl.col1 = dups.col1
and tbl.col2 = dups.col2
order by tbl.col1,tbl.col2

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-02 : 06:41:11

thanks Rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-02 : 06:44:27

Now how do i check to see the duplicates fields in the same table.

Afrika

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-02 : 06:47:02

Could You post the table structure and some more specifics.
The query I posted was for duplicates in same table for 2 columns.
Maybe I don't understand what You are after.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-02 : 06:49:25

Sorry, my bad:
select tbl.col1,tbl.col2,tbl.*
from tbl
join (select col1,col2 from tbl group by col1,col2 having count(*) > 1) as dups
on tbl.col1 = dups.col1
and tbl.col2 = dups.col2
order by tbl.col1,tbl.col2

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-02 : 07:03:00

hi Rockmoose
am just checking one table now, below
code
query1: select distinct(EntryRegNo) from studentadmission where currentsession = '2004-2005'
query2: select EntryRegNo from studentadmission where currentsession = '2004-2005'

total records query 1 = 430,100
query 2: 430,067
difference. 37

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-02 : 07:19:30

Try this;
select * from studentadmission
where currentsession = '2004-2005'
and EntryRegNo in(
select EntryRegNo from studentadmission
where currentsession = '2004-2005'
group by EntryRegNo having count(*) > 1)

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-02 : 07:34:23

hi Rockmoose,

Am getting 74 records. Howver i am going through them to double check .

'cause if i run
select distinct i get 37 records.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-02 : 07:38:42

Yes, since they are duplicated You will get 74.
distinct = 37, sounds like there are 2 each = 74.

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-04-02 : 07:51:06

Oh ok,
i just noticed your second query uses the in keyword.

Gracia mon ami