The following query will return 3 word in 3 "TABLE COLUMN", How can I
modify the query to return "NO 3 words are the same"?

the following Table will have duplicated as both row has the same 3
words, I only need "a, young, woman" is enough. I do not need "a,
woman, young"
=======================
a, young, woman <-- both row has same 3 word, I only need 1 row
a, woman, young
=======================
(word1)(word2) (word3)
a, young, woman
a, woman, young
a, fish, dive
.....

=======================
SELECT DISTINCT A.word As word1, B.word AS word2, C.word AS word3

FROM Data AS A

JOIN Data AS B

ON A.document = B.document

AND A.position < B.position

AND ABS(A.position - B.position) < 3

LEFT JOIN Data AS C

ON A.document = C.document

AND A.position <> C.position

AND C.position <> B.position

AND (ABS(A.position - C.position) < 3

OR ABS(B.position - C.position) < 3) order by word1;

"Carl Forsman" <fatwallet951 (AT) yahoo (DOT) com> a écrit dans le message de news: 2dfgq4lnlp186te0dcb3hoduur8fu7va2b (AT) 4ax (DOT) com...
Use greatest and least functions.

Regards
Michel

On Fri, 27 Feb 2009 20:37:54 +0100, "Michel Cadot"
<micadot{at}altern{dot}org> wrote:

but the query returns 2 row has the same word

(word1)(word2) (word3)
a, young, woman
a, woman, young
a, fish, dive

===================
since "a,young,woman" and "a,woman,young" are duplicated
I only want query return
(word1)(word2) (word3)
a, young, woman
a, fish, dive

-OR-

(word1)(word2) (word3)
a, woman, young
a, fish, dive

"Carl Forsman" <fatwallet951 (AT) yahoo (DOT) com> a écrit dans le message de news: v8igq45d4jo0q81lvr0ja14mv4etd45e9a (AT) 4ax (DOT) com...
Yes and try what I have said.
And if you want an example you MUST post a test case: create table and insert statements.

Regards
Michel

Carl Forsman wrote:

Don;t multi-post use cross-post instead. same question is in the MySQL
NG - are you using Oracle or MySQL - your answer could be different.

sounds like homework to me...

Carl Forsman schrieb:

Your rows are not duplicated. They could be it, if you were able to
rearrange words within a row in some order. And Michel already told you,
how can it be done...

SQL> with t as (
2 select 'a' word1,'young' word2,'woman' word3 from dual union all
3 select 'a','woman','young' from dual union all
4 select 'young','woman','a' from dual union all
5 select 'a','fish','dive' from dual union all
6 select 'young','woman','b' from dual union all
7 select 'woman', 'b','young' from dual union all
8 select 'b','fish','dive' from dual
9 )
10 -- End test data
11 select word1, word2, word3, number_of_sets
12 from (
13 select word1,
14 word2,
15 word3,
16 least(word1, word2, word3) first,
17 least(greatest(word1, word2),
18 greatest(word1, word3),
19 greatest(word2, word3)) second,
20 greatest(word1, word2, word3) third,
21 row_number() over(partition by
22 least(word1, word2, word3),
23 least(greatest(word1, word2),
24 greatest(word1, word3),
25 greatest(word2, word3)),
26 greatest(word1, word2, word3) order
by null) sentence,
27 count(*) over(partition by
28 least(word1, word2, word3),
29 least(greatest(word1, word2),
30 greatest(word1, word3),
31 greatest(word2, word3)),
32 greatest(word1, word2, word3) order
by null) number_of_sets
33 from t)
34 where sentence = 1
35 ;

WORD1 WORD2 WORD3 NUMBER_OF_SETS
----- ----- ----- --------------
a fish dive 1
a woman young 3
b fish dive 1
young woman b 2

Best regards

Maxim

Maxim Demenko wrote:

Boy, I would hate to see that run over a table with 1M+ of rows...

Michael Austin schrieb:
I feel with you ...
Though, this particular example with 1M+ rows and average rowlength of
11 Byte would mean nearly 10Mb data to read, you wouldn't probably ever
notice a trace of load ... Even worse, one can consider to run it on
MySQL (ISAM) - may be a tick faster than Oracle :-)

Seriously, though this may be interesting puzzle to solve in sql, it
doesn't look like properly designed structure for relational database.

Best regards

Maxim