Well for the sample data u have provided it works.. though u'll have to
tweak it a bit to use it in ur enterprise software(nulls, u also have to
ytake care of rownum part... remove it)
I was mistaken in my reply earlier
Kindly excuse.
Abhijith
abhijih wrote:
>
> Yes.
>
> Joe Smith wrote:
>
>> "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 - 08:26:47 CDT
