Re: Complicated SQL question

neurotoxin2309_at_yahoo.com wrote:
> Good morning,
> We have recently started archiving all of our mail headers in Oracle,
> while keeping our mail files on the local drive. Our table structure
> for our archiving is as follows:
>
> EMAIL_DIRECTION
> MESSAGE_DIRECTION NOT NULL NUMBER(1)
> DESCRIPTION NOT NULL VARCHAR2(4)
> 1 From
> 2 To
> 3 Cc
> 4 Bcc
>
> EMAIL_ID_X_ADDRESS
> EMAIL_ID NOT NULL NUMBER(10)
> ADDRESS NOT NULL VARCHAR2(64)
>
> MESSAGE_DIRECTION
> MESSAGE_NO NOT NULL NUMBER(8)
> DIRECTION NOT NULL NUMBER(1)
> EMAIL_ID NOT NULL NUMBER(10)
>
> MESSAGE_HEADER
> MESSAGE_NO NOT NULL NUMBER(8)
> SUBJECT VARCHAR2(120)
> MESSAGE_DATE NOT NULL DATE
> TYPE NUMBER(1)
>
> We've done a job of normalizing our tables, but now I'm having
> trouble making a proper query for this data.
>
> I am attempting to determine which message_no's have the highest
> number of recipients, and print out the sender, subject line, date and
> other statistics for that message. The problem is, the only way I have
> been able to do this is with three or four queries, excel, and a lot of
> cutting and pasting.
>
> Can anyone come up with a single query to determine this data?
>
> Thanks for all your help.

It can be done with one query but it would be instructive if ou would post yours so we can better see what you are doing.

SELECT MAX(COUNT(.. will give you maximum number of receipients if put into an in-line view. You could then query that or use the same SQL as a subquery.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)