Re: Simple SQL (or complex???)

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 20 Mar 2007 17:38:29 +0100
Message-ID: <46000e06$0$24794$426a34cc@news.free.fr>

<P_swiggers_at_yahoo.com> a écrit dans le message de news: 1174376055.413150.149220_at_e65g2000hsc.googlegroups.com...
| 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)

Surely but why as it will certainly be much less efficient? Analytic functions are not complex they are powerful so maybe the syntax is a little bit obscure but it worth to learn it.

Regards
Michel Cadot Received on Tue Mar 20 2007 - 11:38:29 CDT