Re: Help with query...

Thank you so much!
I had never used or found an "analytic clause" (Oracle's SQL documentation :) ).

Bye!

"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0306170706.4cd691a_at_posting.google.com...
> This should work:
>
> with v1 as (select a, b, count(1) cnt from t1_ group by a,b order by 1,3
desc)
> select a, total, b, cnt from (
> select a, b, sum(cnt) over (partition by a) total, cnt,
> row_number() over (partition by a order by cnt desc) rnk from
v1)
> where rnk=1;
>
> Basically you count by group a and b,
> and then sum them up and pick the right row to return.
>
> - Jusung Yang
>
>
> "Joe Smith" <nospam_at_nospam.com> wrote in message
news:<bcmke8$c0n$1_at_news-reader14.wanadoo.fr>...
> > Hi,
> > I'm having trouble with a query, and I'm not sure if it's feasible with
> > 'simple' SQL (not PL/SQL). I'm using 8.1.7
> > Given a table like this:
> >
> > A B
> > ========
> > a w
> > a x
> > a w
> > a y
> > b z
> > b x
> > b z
> > b z
> > c y
> > c x
> > c x
> > d z
> > d y
> >
> > a, b, c, d, x, y, z can be any value, are not fix strings or values...
> >
> > I'd like to get something like:
> >
> > a 4 w 2
> > b 4 z 3
> > c 3 x 2
> > d 2 z 1
> >
> > Read as:
> > column 1: element
> > column 2: number_of_appereances
> > column 3: B element that appears most times
> > column 4: number of appereances of the element in 3
> >
> > Would this be possible??
> > I hope this doesn't seem like homework this time :). I've really tried
to do
> > it myself, I don't like asking for help all the time.
> > If you want to know where this comes from, the first column is a sender,
and
> > the second is a receiver, so the goal is to know how many communications
> > each sender has stablished, who has been his "preferred" receiver, and
which
> > is the ratio preferred/total.
> >
> > Thanks in advance!
Received on Tue Jun 17 2003 - 11:26:19 CDT