Getting duplicate columns - 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

General SQL Server Forums

New to SQL Server Programming

Getting duplicate columns

Author

Topic

Vishakha
Starting Member

40 Posts

Posted - 2007-04-09 : 07:57:15

Hello,
I have a table with say 45 columns.
I have a business requirement that requires me to fetch the rows for which col1 , col2, ....col 11 are same and rest can be different. there is an identity column, in the table so I can have duplicate rows also.

how can I effectively write a query that will fetch me all those rows for which my 11 columns are same.

Thanks,
Vishakha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 07:58:54

[code]
select *
from yourtable
where co1l = col2
and col2 = col3
and . . .
and col10 = col11
[/code]

Vishakha
Starting Member

40 Posts

Posted - 2007-04-09 : 08:11:56

my data is going to be like this
col1 col2 col3 ....col11 col12.....col45
a b c m ................
a y c t ....................
a b c m ................

now what i want is get all the rows for which col1 to col11 matches
like 1st and 3rd row.

Sorry if I was not clear in my earlier post.

Thanks,
Vishakha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 08:14:50

[code]
select t.*
from yourtable t
inner join
(
select col1, col2, ... col11
from yourtable x
group by col1, col2 . . . col11
) d
on t.col1 = d.col1
and t.col2 = d.col2
. . .
and t.col11 = d.col11
[/code]

Vishakha
Starting Member

40 Posts

Posted - 2007-04-09 : 08:19:29

Thanks,
This is what we are doing right now, but doing this is all the
rows from the table are returned, whereas what I want is only the duplicate ones.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 08:21:27

sorry.. missed out the group by statement


select t.*
from   yourtable t
inner join 
(
    select col1, col2, ... col11
    from   yourtable x
    group by col1, col2 . . . col11
    having count(*) > 1
) d
on  t.col1 = d.col1
and t.col2 = d.col2
. . .
and t.col11 = d.col11

Vishakha
Starting Member

40 Posts

Posted - 2007-04-09 : 08:25:49

Thanks adding having clause to the group by statement worked.

I also missed the having clause in my initial query.

Thanks much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources