Re: Help with query...

From: abhijih <abhijit_at_nomail.com>
Date: Wed, 18 Jun 2003 18:40:05 +0530
Message-ID: <3EF064AD.70108@nomail.com>

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:10:05 CDT