Re: column filter

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 28 Feb 2009 11:29:47 +0100
Message-ID: <49A9121B.4090300_at_gmail.com>

Michael Austin schrieb:

> 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... :)

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 Received on Sat Feb 28 2009 - 04:29:47 CST