Re: Help with query...

From: Abhijith <abhijith.kashyap_at_oracle.com>
Date: Wed, 18 Jun 2003 15:14:34 +0530
Message-ID: <3EF03482.1060307@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.

    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 - 04:44:34 CDT