InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 02:36:35 GMT20http://www.sqlservercentral.com/Forums/Topic1415720-146-1.aspx[quote][b]Johny.B (2/8/2013)[/b][hr]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 wantselect * 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 aleft join T1 b on a.sl<>b.sl) cwhere c.ARE_THE_SAME='YES'[/quote]You can accomplish the same thing with the subselect. Just do the filtering in your join.[code]select 'Yes' as ARE_THE_SAME,a.*from #T1 aleft join #T1 b on a.sl<>b.sl and a.isdn=b.isdn and a.msg=b.msgwhere b.sl is not null[/code]Not sure that is the way I would do that but it is simpler.Given the OP's comment [quote]Now I wanna compare whether there is any similar row comprising ISDN and MSG columns.[/quote]Maybe this is what they are after?[code]select ISDN, MSG, count(*) from #t1group by ISDN, MSGhaving count(*) > 1[/code]Who really knows? The questions and the follow up description just don't have much detail to work with.Fri, 08 Feb 2013 09:43:23 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1415720-146-1.aspxAnother 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 wantselect * 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 aleft join T1 b on a.sl<>b.sl) cwhere c.ARE_THE_SAME='YES'Fri, 08 Feb 2013 09:08:00 GMTJohny.Bhttp://www.sqlservercentral.com/Forums/Topic1415720-146-1.aspxHi, 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):[code="sql"]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 illustrationinsert 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, MSGfrom #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=1union allselect * from cte where instanceNumber>1drop table #t1[/code]Thu, 07 Feb 2013 16:29:07 GMTmmartin1http://www.sqlservercentral.com/Forums/Topic1415720-146-1.aspxWhat 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?Wed, 06 Feb 2013 09:50:41 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1415720-146-1.aspxThank you, OPC.THREE[code="sql"]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');[/code]Now I wanna compare whether there is any similar row comprising ISDN and MSG columns.Wed, 06 Feb 2013 03:16:14 GMTshohelr2003http://www.sqlservercentral.com/Forums/Topic1415720-146-1.aspxT-SQL supports all your common comparison operators, e.g. = <>!= [i]or[/i] <>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.Tue, 05 Feb 2013 05:40:10 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1415720-146-1.aspxDear,I want to compare string type values between two rows within a table.Pls help me to do this.Tue, 05 Feb 2013 04:18:04 GMTshohelr2003