Exclude similar records from table

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

Exclude similar records from table

Author

Topic

SunnyIngulia
Starting Member

2 Posts

Posted - 2010-10-12 : 06:02:59

I am using SQL Server 2008. So according to the task: if the table consists records like this:

id1 name1 id2 name2
1 a 2 b
2 b 1 a
1 a 3 c
3 c 1 a
2 b 3 c

the result must be:

id1 name1 id2 name2
1 a 2 b
1 a 3 c
2 b 3 c

So as you see rows 1 a 2 b and 2 b 1 a must represent as the same(dublicate)
How can I exclude rows that consist the same data in different order?

SunnyIngulia

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 09:03:13

[code]
declare @tbl table(id int identity,id1 char(40),name1 char(40),id2 char(40),name2 char(40))
insert into @tbl
select '1', 'a', '2', 'b'union all
select '2', 'b', '1', 'a'union all
select '1', 'a', '3', 'c'union all
select '3', 'c', '1', 'a'union all
select '2', 'b','3', 'c'

;with cte
as
(
select *,row_number()over(partition by sm order by id)rid from
(
select sum(rid)sm,id from
(
select *,
dense_rank()over(order by col)rid
from
(
select * from @tbl)u
unpivot
(col for columns in(id1,name1,id2,name2))v
)T
group by id
)T1

)

select t.* from cte inner join @tbl t on t.id=cte.id where rid=1 order by cte.id
[/code]

PBUH