Query to Exclude Duplicate values in different columns.
-
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.:-)
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)
-
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 -
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.:-)
-
The second one đŸ™‚ . I at least need them once.
-
Thanks Siva!! Hope this fits best đŸ™‚ đŸ™‚
-
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 -
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
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);- Forum Etiquette: How to post Reporting Services problems
[/url]- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]- How to Post Performance Problems - by Gail Shaw
[/url] -
@SSC Veteran. Yes,the ranking is out of line. let me try the other one. But big thanks to all đŸ™‚
-
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.
-
Thanks SSC Eights!
Your query works perfect.
Big thanks to all for your precious time!
Regards,
Asrar
-
SELECT nc1,class,nc2
FROM #nc
EXCEPT
SELECT nc2,class,nc1
FROM #nc
WHERE nc2 > nc1
ORDER BY class,nc1,nc2
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Another way and very fast:
SELECT nc1, class, nc2 FROM #nc t WHERE NOT EXISTS (SELECT 1 FROM #nc t0 WHERE t0.nc1 = t.nc2 AND t0.nc2 = t.nc1 AND t.nc2 < t0.nc2)
Pedro
If you need to work better, try working less... -
PiMané (10/1/2012)
Another way and very fast:SELECT nc1, class, nc2 FROM #nc t WHERE NOT EXISTS (SELECT 1 FROM #nc t0 WHERE t0.nc1 = t.nc2 AND t0.nc2 = t.nc1 AND t.nc2 < t0.nc2)
Pedro
I think you're missing "AND t0.class=t.class" in the subquery, otherwise with the data below one of the rows is removed
insert into #nc
values(123,10,456),(456,11,123)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thanks everybody!! that really helped . đŸ™‚
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply