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'

Now I wanna compare whether there is any similar row comprising ISDN and MSG columns.