Re: Simple SQL (or complex???)

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 20 Mar 2007 00:33:37 +0100
Message-ID: <45FF1DD1.4000402@gmail.com>

P_swiggers_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 ago http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683#168581900346859639 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 Received on Mon Mar 19 2007 - 18:33:37 CDT