Re: column filter
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 28 Feb 2009 05:53:10 +0100
Message-ID: <49A8C336.5010200_at_gmail.com>
Carl Forsman schrieb:
> On Fri, 27 Feb 2009 20:37:54 +0100, "Michel Cadot"
> <micadot{at}altern{dot}org> wrote:
>
>> "Carl Forsman" <fatwallet951_at_yahoo.com> a ?crit dans le message de news: 2dfgq4lnlp186te0dcb3hoduur8fu7va2b_at_4ax.com...
>
> 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
10 -- End test data
11 select word1, word2, word3, number_of_sets 12 from (
34 where sentence = 1
35 ;
Date: Sat, 28 Feb 2009 05:53:10 +0100
Message-ID: <49A8C336.5010200_at_gmail.com>
Carl Forsman schrieb:
> On Fri, 27 Feb 2009 20:37:54 +0100, "Michel Cadot"
> <micadot{at}altern{dot}org> wrote:
>
>> "Carl Forsman" <fatwallet951_at_yahoo.com> a ?crit dans le message de news: 2dfgq4lnlp186te0dcb3hoduur8fu7va2b_at_4ax.com...
>> Use greatest and least functions. >> >> Regards >> Michel >>
>
> 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
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 dual9 )
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 Received on Fri Feb 27 2009 - 22:53:10 CST