Re: Simple SQL (or complex???)

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 21 Mar 2007 21:22:00 +0100
Message-ID: <ets45h$aci$01$1@news.t-online.com>

P_swiggers_at_yahoo.com schrieb:
> On Mar 20, 12:33 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:

>> P_swigg..._at_yahoo.com schrieb:
>>
>>
>>
>>
>>
>>> I'm looking for some help on SQL.
>>> very simple table:
>>> SQL> select * from patt;
>>>        CMB COD
>>> ----------    --------
>>>          1    A
>>>          1    B
>>>          1    C
>>>          2    X
>>>          2    B
>>>          2    C
>>>          3    A
>>>          3    B
>>>          3    C
>>> As I can see there are :
>>> 2 sets of same COD: A,B,C (for CMB 1 & 3) with max(CMB)=3
>>> 1 set of same COD: X,B,C (for CMB 2) with max(CMB)=2
>>> However, I cannot get my query to get result:
>>> max(CMB)  count(*)
>>> 3               2
>>> 2               1
>>> Hallelujah Hollywood for who can give me the sql...:-))
>> In the link which Michel Cadot posted in this group 4 days agohttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...
>> you can find some approaches to solve similar problem.
>> One would be
>>
>> SQL> with patt as(
>>    2          select 1 cmb,'A' cod from dual union all
>>    3          select 1,'B' from dual union all
>>    4          select 1,'C' from dual union all
>>    5          select 2,'X' from dual union all
>>    6          select 2,'B' from dual union all
>>    7          select 2,'C' from dual union all
>>    8          select 3,'A' from dual union all
>>    9          select 3,'B' from dual union all
>>   10          select 3,'C' from dual),
>>   11  t as (
>>   12          select
>>   13          patt.*,
>>   14          dense_rank() over(order by cod) -1 dr
>>   15          from patt),
>>   16  t1 as(
>>   17          select
>>   18          t.cmb,
>>   19          sum(power(2,dr)) group_id
>>   20          from t
>>   21          group by cmb
>>   22  )
>>   23  select max(cmb),count(*)
>>   24  from t1
>>   25  group by group_id
>>   26  /
>>
>>    MAX(CMB)   COUNT(*)
>> ---------- ----------
>>           2          1
>>           3          2
>>
>> Best regards
>>
>> Maxim- Hide quoted text -
>>
>> - Show quoted text -

>
> Hi Maxim,
> Thanks for this.
> Is there any way to write this sql without using complex functions
> such as dense_rank()?
> Only by using inline views and 'where exists' clauses or similar. Or
> even to spilt the sql in parts using PL/SQL.
> Cheers,
> Patrick Swiggers
> (Belgium)
>
>
>
>

If you would follow the link, you would see suggested solutions. One of them is to pivot the query, so similar subsets could be grouped together. Another - to use model clause to concatenate subset member to one string and so compare the subsets. In pl sql it would be most easiest to implement, because here you can introduce any number of variables to store intermediate results, move forward and backward within result set and so on. But it would be not as performant as only SQL solution.
Bottom line, i agree with Michel on this - analytics are almost 10 years available, the solution seems to be pretty natural and by far not complex (in opposite to the task - it is not trivial, to compare subsets with pure SQL).

Best regards

Maxim Received on Wed Mar 21 2007 - 15:22:00 CDT