Re: column filter

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 27 Feb 2009 23:06:25 -0600
Message-ID: <EB3ql.8314$%54.4548_at_nlpi070.nbdc.sbc.com>

Maxim Demenko wrote:
> 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 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

Boy, I would hate to see that run over a table with 1M+ of rows... :) Received on Fri Feb 27 2009 - 23:06:25 CST