want to remove duplicate combination from self join result

[Rishi Sarawagi]

1)Write a query that produces all pairs of salespeople who are living
in the same city. Exclude combinations of salespeople with themselves
as well as duplicate rows with the order reversed.


my table is:


SNUM SNAME CITY COMM
---------- ---------- - --------- ----------
1001 Peel London .12
1002 Serres San Jose .13
1004 Motika London .11
1007 Rifkin Barcelona .15
1003 Axelrod New York .1

i am doing this:


SQL> select s1.sname,s2.sname from salespeople s1 join salespeople s2
on
2 s1.city=s2.city and s1.snum<>s2.snum;

SNAME SNAME
---------- ----------
Motika Peel
Peel Motika

i want to remove this combination.

[Paul Collins]

Hi Rishi,

 

Try using s1.snum < s2.snum.

 

thsi should remove any duplications.

 

Cheers,

 

Paul

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[rishi sarawagi]

thanks paul.

It's working.