Help with query...

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 - 03:44:55 CDT