DISTINCT with Self_Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DISTINCT with Self_Join

Author

Topic

haido
Starting Member

2 Posts

Posted - 2006-08-05 : 05:08:43

hello ppl. would appreciate some assistance here..

trying to find distinct PAIR of names using self join but having some problems.. my SELECT statement goes like this:

SELECT s1.name AS Name1, s2.name AS Name2
FROM shops e1, shops e2
WHERE s1.street = s2.street
AND s1.name != s2.name;

Results
Row Name1 Name2
1 LONG JOHN LAZY LOBSTER
2 LAZY LOBSTER LONG JOHN
3 SWEET DREAMS LITTLE PIRATE
4 LITTLE PIRATE SWEET DREAMS

Row (1 and 2) and (3 and 4) are duplicates! how can I stop this duplicating?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-05 : 05:19:57

Somthing like this

]
Declare @Shop Table
(
Name1 varchar(20),
street int 
)

Insert @Shop
Select 'LONG JOHN', 1 Union All 
Select 'LAZY LOBSTER',1 Union All 
Select 'SWEET DREAMS',2 Union All 
Select 'LITTLE PIRATE',2 


SELECT MAx(e1.name1) AS Name1, Min(e2.name1) AS Name2
FROM @Shop e1, @Shop e2
WHERE e1.street = e2.street
AND e1.name1 != e2.name1
Group by e1.street

Chirag

haido
Starting Member

2 Posts

Posted - 2006-08-05 : 11:44:57

Hi Chirag, thanks for your reply but i got

"ORA-00979: not a GROUP BY expression"

when i attempt with your select statement. And, the tables and inputs are already pre-determined, which means I cannot make changes to them.

Here is the table and its inputs

CREATE TABLE shops(
NAME VARCHAR(30) NOT NULL,
STREET VARCHAR(30) NOT NULL,
BLDG# NUMBER(4) NOT NULL,
CONSTRAINT LOCATED_PKEY PRIMARY KEY(NAME) );

INSERT INTO shops VALUES('LAZY LOBSTER', 'STATION ST.', 45);
INSERT INTO shops VALUES('GREASY FORK', 'VICTORIA AVE.', 345);
INSERT INTO shops VALUES('CAPTAIN MOORE', 'KING ST.', 45);
INSERT INTO shops VALUES('LONG JOHN', 'STATION ST.', 89);
INSERT INTO shops VALUES('LITTLE PIRATE', 'OXFORD ST.', 345);
INSERT INTO shops VALUES('SWEET DREAMS', 'OXFORD ST.', 267);

Would appreciate any further help. Thanks in advance!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-07 : 07:01:49

Well i tried your Query on the ORACLE 10g,
it didnt give me any errors


SELECT MAx(e1.name) AS Name1, Min(e2.name) AS Name2
FROM Shops e1, Shops e2
WHERE e1.street = e2.street
AND e1.name != e2.name
Group by e1.street;

--Ouput 
NAME1                          NAME2                         
------------------------------ ------------------------------
SWEET DREAMS                   LITTLE PIRATE                 

1 row selected

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources