Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing two rows Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 4:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
Dear,
I want to compare string type values between two rows within a table.

Pls help me to do this.
Post #1415720
Posted Tuesday, February 5, 2013 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
T-SQL supports all your common comparison operators, e.g.

=
<
>
!= or <>

Comparisons involving two rows might imply the comparison would take place in a WHERE-clause. If you provide table definitions (CREATE TABLE statements), some test data (INSERT INTO statements) and a better idea of what you're trying to do I'd be happy to further help you.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1415768
Posted Wednesday, February 6, 2013 3:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
Thank you, OPC.THREE

create table T1(
SL int NOT NULL IDENTITY (1, 1),
ISDN varchar(13) NULL,
MSG varchar(50) NULL
)

insert into T1(ISDN,MSG) values ('123','ABC');
insert into T1(ISDN,MSG) values ('123','ABC');
insert into T1(ISDN,MSG) values ('123','XYZ');
insert into T1(ISDN,MSG) values ('124','XYZ');
insert into T1(ISDN,MSG) values ('124','EFG');

Now I wanna compare whether there is any similar row comprising ISDN and MSG columns.
Post #1416360
Posted Wednesday, February 6, 2013 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
What is the goal? A set of rows with no duplicates? If so, would you want the row with the max ID? the min ID?

Most importantly, what have you tried so far?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1416611
Posted Thursday, February 7, 2013 4:29 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
Hi, The way I read it you are looking for not only duplicates but the original row the duplicates duplicated?

If so try this ( I adjusted your code a tad):

create table #T1(
SL int NOT NULL IDENTITY (1, 1),
ISDN varchar(13) NULL,
MSG varchar(50) NULL
)

insert into #T1(ISDN,MSG) values ('123','ABC');
insert into #T1(ISDN,MSG) values ('123','ABC');
insert into #T1(ISDN,MSG) values ('123','XYZ');
insert into #T1(ISDN,MSG) values ('124','XYZ');
insert into #T1(ISDN,MSG) values ('124','EFG');

--extra duplicate added for better illustration
insert into #T1(ISDN,MSG) values ('123','ABC');


WITH CTE AS (
select row_number() over(partition by isdn, msg order by isdn, msg) as instanceNumber,
SL, ISDN, MSG
from #t1
)


select lt.* from cte as lt inner join (select * from cte where instanceNumber=2) as rt
on lt.isdn=rt.isdn and lt.msg=rt.msg and lt.instanceNumber=1
union all
select * from cte where instanceNumber>1


drop table #t1

Post #1417385
Posted Friday, February 8, 2013 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 1, 2013 2:50 AM
Points: 2, Visits: 23
Another solution that uses left join and a [ARE_THE_SAME] column to mark the columns having same values, you can modify the comparison logic based on what you want to achieve.

The left outer join on a.sl<>b.sl have similar results to a cross join but does not return the same matching rows. a cross join for the example would return 5*5=25 rows the outer returns 5*4=20, the 1-1,2-2,3-3,4-4,5-5 cases does not returned from the join cause of the a.sl<>b.sl .

the outer select is just for filter the cases that we want


select * from
(
select
case when a.isdn=b.isdn and a.msg=b.msg then 'YES' else 'NO' END as [ARE_THE_SAME],a.*
from T1 a
left join T1 b on a.sl<>b.sl
) c
where c.ARE_THE_SAME='YES'
Post #1417767
Posted Friday, February 8, 2013 9:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 13,085, Visits: 12,550
Johny.B (2/8/2013)
Another solution that uses left join and a [ARE_THE_SAME] column to mark the columns having same values, you can modify the comparison logic based on what you want to achieve.

The left outer join on a.sl<>b.sl have similar results to a cross join but does not return the same matching rows. a cross join for the example would return 5*5=25 rows the outer returns 5*4=20, the 1-1,2-2,3-3,4-4,5-5 cases does not returned from the join cause of the a.sl<>b.sl .

the outer select is just for filter the cases that we want


select * from
(
select
case when a.isdn=b.isdn and a.msg=b.msg then 'YES' else 'NO' END as [ARE_THE_SAME],a.*
from T1 a
left join T1 b on a.sl<>b.sl
) c
where c.ARE_THE_SAME='YES'


You can accomplish the same thing with the subselect. Just do the filtering in your join.

select
'Yes' as ARE_THE_SAME,
a.*
from #T1 a
left join #T1 b on a.sl<>b.sl and a.isdn=b.isdn and a.msg=b.msg
where b.sl is not null

Not sure that is the way I would do that but it is simpler.

Given the OP's comment
Now I wanna compare whether there is any similar row comprising ISDN and MSG columns.


Maybe this is what they are after?

select ISDN, MSG, count(*) 
from #t1
group by ISDN, MSG
having count(*) > 1

Who really knows? The questions and the follow up description just don't have much detail to work with.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1417796
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse