Query to Exclude Duplicate values in different columns.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Query to Exclude Duplicate values in...

14 posts, Page 1 of 2

1 2 »»

Query to Exclude Duplicate values in different columns.

Rate Topic

Display Mode

Topic Options

Author

Message

Syed Asrar Ali

Posted Monday, October 01, 2012 4:02 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 1:09 AM
Points: 11, Visits: 81

Can anybody help me with this logic.

I want to exclude rows in which value for column nc1 is repeated in column nc2 and value for column nc2 is repeated in column nc1 having class value same.

below is the sample table with few values.Actual table is very huge.

In case :
nc1 class nc2
110, 1 112
112 1 110

i do not want to repeat this in my result set as the values of nc1 and nc2 are interchanged and the value of class is same.

Thanks all for you time. Smile

create table #nc (nc1 int,class int,nc2 int)
 
 insert into #nc
 values(110,1,112)
	  ,(112,1,110)
	  ,(210,2,212)
	  ,(310,3,313)
	  ,(313,3,310)
	  ,(410,1,141)
	  ,(329,7,231)

Post #1366455

Jason-299789

Posted Monday, October 01, 2012 4:17 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 836, Visits: 2,192

So do you want both rows excluding or only one of the rows?

Eg which data set do you want to see

210,2,212
410,1,141
329,7,231

or you need the data set to look like

110,1,112
210,2,212
310,3,313
410,1,141
329,7,231

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1366461

sivaj2k

Posted Monday, October 01, 2012 4:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 2:46 AM
Points: 45, Visits: 420

Hi
This may help you.

create table #nc (nc1 int,class int,nc2 int)

insert into #nc
values(110,1,112)
,(112,1,110)
,(210,2,212)
,(310,3,313)
,(313,3,310)
,(410,1,141)
,(329,7,231)

select * from #nc

select nc1,nc2,class from
(select *,row_number() over (partition by rnk order by rnk) rnk1 from(
select * from
(
select nc1,nc2,class,dense_rank() over (order by (nc1+nc2)/2) rnk from #nc
)a
)b
)c where rnk1 =1 order by 3

drop table #nc

Regards
Siva Kumar J. Smile

Post #1366464

Syed Asrar Ali

Posted Monday, October 01, 2012 4:25 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 1:09 AM
Points: 11, Visits: 81

The second one :) . I at least need them once.

Post #1366465

Syed Asrar Ali

Posted Monday, October 01, 2012 4:30 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 1:09 AM
Points: 11, Visits: 81

Thanks Siva!! Hope this fits best :) :)

Post #1366467

Jason-299789

Posted Monday, October 01, 2012 4:35 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 836, Visits: 2,192

select nc1,nc2,class from
(select *,row_number() over (partition by rnk order by rnk) rnk1 from(
select * from
(
select nc1,nc2,class,dense_rank() over (order by (nc1+nc2)/2) rnk from #nc
)a
)b
)c where rnk1 =1 order by 3

Theres a flaw the logic here, if you add the row (230,7,330) to the data set, it doesnt get returned even though it isnt a duplicate.

EDIT : Quoted wrong post.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1366471

mister.magoo

Posted Monday, October 01, 2012 4:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 1,333, Visits: 4,015

This is one way to go...


select min(class) as class,x.nc1, x.nc2
from #nc 
cross apply (select case when nc1<nc2 then nc1 else nc2 end,case when nc1<nc2 then nc2 else nc1 end) x(nc1,nc2)
group by x.nc1, x.nc2
order by class,nc1,nc2

Post #1366475

Syed Asrar Ali

Posted Monday, October 01, 2012 4:47 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 1:09 AM
Points: 11, Visits: 81

@SSC Veteran. Yes,the ranking is out of line. let me try the other one. But big thanks to all :)

Post #1366479

sivaj2k

Posted Monday, October 01, 2012 5:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 2:46 AM
Points: 45, Visits: 420

Hi

Yes, There is a flaw in the previous logic. Cool

Here is corrected version

create table #nc (nc1 int,class int,nc2 int)

insert into #nc
values(110,1,112)
,(112,1,110)
,(210,2,212)
,(310,3,313)
,(313,3,310)
,(410,1,141)
,(329,7,231)
,(330,7,230)

select * from #nc

select min(class) class,a.nc1,a.nc2 from
(
select class ,case when nc1<nc2 then nc1 else nc2 end nc1,
case when nc1<nc2 then nc2 else nc1 end nc2
from #nc
) a group by a.nc1,a.nc2 order by class,nc1,nc2

drop table #nc

Regards
Siva Kumar J.

Post #1366481

Syed Asrar Ali

Posted Monday, October 01, 2012 5:10 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 1:09 AM
Points: 11, Visits: 81

Thanks SSC Eights!

Your query works perfect.

Big thanks to all for your precious time!

Regards,
Asrar

Post #1366486

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions