Re: Help with query...

"Abhijith" <abhijith.kashyap_at_oracle.com> wrote in message news:3EF03482.1060307_at_oracle.com...
> A more amatueristic approach, might as well do for college homework...
> more like the one u wud do in a 3 hr semester exam.

I'm getting fed up of this kind of answers... Sorry if it seems so easy for you. If you want to help, thanks, it's enough with the answer. What do you get by 'qualifying' the level of my question? As I said, I'm not a student. Right, it's up to you to believe or not.

Are you the one who rules the kind of questions that can or can't be asked here?

By the way, your answer doesn't seem to work. Have you checked it?

>
> SELECT T.A A1, COUNT(T.A) A2,
> ( select b from temp g where a =t.a and rownum = 1
> group by b having count(g.b)=(select max(count(l.b)) from temp l
> where l.a = t.a and rownum =1 group by l.b)) as A3,
> (select max(count(b)) from temp where a = t.a group by
> b) as A4
> FROM TEMP T
> GROUP BY T.A
>
> Might as well be liked by the prof. enuf to get thru with it.
> Abhijith
>
> Joe Smith wrote:
> > 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 Wed Jun 18 2003 - 07:38:26 CDT