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...
>> 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 Received on Fri Feb 27 2009 - 22:53:10 CST

Original text of this message