SQL find similar rows without having all combination
I have this SQL table:
CREATE TABLE DATA (
ID NUMBER NOT NULL,
CODE VARCHAR(32) NOT NULL,
DESCRIPTION VARCHAR(256),
PRIMARY KEY (ID)
);
and some values in it like:
ID CODE DESC
1 'A' 'AAAA'
2 'B' 'BBB'
3 'A' 'AGAIN'
4 'C' 'CCC'
5 'A' 'ABBA'
I want to retrieve all lines with similar code because I need to add a UNIQUE constraint on the code column.
I have this query:
select distinct
t1.code, t1.id, t1.description, t2.id, t2.description
from
data t1, data t2
where
t1.code = t2.code and t1.id != t2.id
order by t1.code
That is returning me this result set:
A 5 ABBA 1 AAAA
A 3 AGAIN 1 AAAA
A 1 AAAA开发者_JAVA百科 5 ABBA
A 1 AAAA 3 AGAIN
A 3 AGAIN 5 ABBA
A 5 ABBA 3 AGAIN
How can I tweak the query to discard combination of already listed differences ? In this example I want to have only this rows:
A 1 AAAA 3 AGAIN
A 1 AAAA 5 ABBA
A 3 AGAIN 5 ABBA
you could take only one of each couple of rows, for instance:
select distinct
t1.code, t1.id, t1.description, t2.id, t2.description
from
data t1, data t2
where
t1.code = t2.code and t1.id < t2.id
order by t1.code
精彩评论