Re: Simple SQL (or complex???)

From: <P_swiggers_at_yahoo.com>
Date: 19 Mar 2007 15:54:32 -0700
Message-ID: <1174344871.980106.163800@e65g2000hsc.googlegroups.com>

On Mar 19, 10:37 pm, DA Morgan <damor..._at_psoug.org> wrote:
> P_swigg..._at_yahoo.com wrote:
> > 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...:-))
>
> Post your query. This looks a lot like homework so it would
> be helpful to see what you did.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Well, I'm from 1969 and I'm working for 10 years as developer but now indeed I start almost crying like a little schoolboy... I started already 20 times on this query. Tried several times something like this:

select max(p.cmb), count(*)
  from patt p,

       (select cmb, cod
          from patt) x

 where p.cmb=x.cmb
   and exists (select 'x'

                 from patt
                where p.cod=x.cod)

 group by p.cmb

MAX(P.CMB) COUNT(*)
---------- ----------

         1          3
         2          3
         3          3

Received on Mon Mar 19 2007 - 17:54:32 CDT