Yes, this was part of an assignment that was due on 15th. I have since
handed in my assignment but I am looking for the answer as to how it shud be
done, so that I can learn from my mistake if it exists.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:uc2hi3d0c8kgff_at_corp.supernews.com...
>
> "Louis Dumont" <dumontls_at_dumontls.com> wrote in message
> news:LK9w8.28503$o66.89058_at_news-server.bigpond.net.au...
> > The following SQL outputs the result that follows:
> >
> > What I really need to output is the person who worked the highest hours
> i.e
> > max(activ_hours) in each day. I have tried every which way and cannot do
> it
> > can somebody please advise how. the result shud only have 5 lines, one
for
> > each day from 18-22 March. tables Im working with are attached
> >
> > SQL> SELECT N.PERS_NAME || ' a ' || P.POSN_NAME || ' in ' || U.UNIT_NAME
> ||'
> > worked ' || max(A.ACTIV
> > _HOURS) || ' hours on ' || TO_CHAR(A.ACTIV_DATE,'fm(DD-Month-YYYY)')
> > 2 FROM POSITION P,PERSONNEL N, ACTIVITY A, UNIT U
> > 3 WHERE P.POSN_ID=N.POSN_ID
> > 4 AND N.PERS_ID=A.PERS_ID
> > 5 AND A.UNIT_ID=U.UNIT_ID
> > 6 GROUP BY N.PERS_NAME, P.POSN_NAME, U.UNIT_NAME, A.ACTIV_HOURS,
> > A.ACTIV_DATE
> > 7 ORDER BY A.ACTIV_DATE;
> >
> >
>
>
>
> SELECT N.PERS_NAME
> || ' a '
> || P.POSN_NAME
> || ' in '
> || U.UNIT_NAME
> ||' worked '
> ||A.ACTIV_HOURS
> || ' hours on '
> || TO_CHAR(A.ACTIV_DATE,'fm(DD-Month-YYYY)')
> FROM POSITION P
> ,PERSONNEL N
> , ACTIVITY A
> , UNIT U
> WHERE P.POSN_ID=N.POSN_ID
> AND N.PERS_ID=A.PERS_ID
> AND A.UNIT_ID=U.UNIT_ID
> and a. activ_hours =
> (select max(activ_hours)
> from activity a1
> where trunc(a1.activ_date) = trunc(a.activ_date)
> order BY
> A.ACTIV_DATE
> , N.PERS_NAME
> , P.POSN_NAME
> , U.UNIT_NAME;
>
>
> This definitely smells like homework, if so, please provide the e-mail
> address of your teacher, so we can send it directly.
>
> --
> Regards
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
Received on Sat Apr 20 2002 - 08:51:56 CDT
