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)
Received on Tue Mar 20 2007 - 02:34:15 CDT
