Re: Oracle/SQL - Doing a 'first' or 'last' in an SQL View/Query aggregate

From: Jan <janik_at_pobox.sk>
Date: 17 May 2004 23:08:26 -0700
Message-ID: <81511301.0405172208.6ddf9018@posting.google.com>

create table t (userid number,value number,dt date);

insert into t(userid,value,dt)

   select mod(rownum,3),dbms_random.random,sysdate-mod(rownum,10)      from all_objects;

SELECT
 userid,
 COUNT(*) cnt,
 MAX(VALUE) max_val,
 MIN(VALUE) min_val,
 fv ,
 lv,
 TRUNC(dt) dt
   FROM (

     SELECT
       userid,
       VALUE,
       dt,
       first_value(VALUE) OVER (PARTITION BY userid,TRUNC(dt,'MON') 
           ORDER BY dt) fv,
       first_value(VALUE) OVER (PARTITION BY userid,TRUNC(dt,'MON') 
           ORDER BY dt DESC) lv

FROM t)
 GROUP BY userid,TRUNC(dt),fv,lv

Jan

P.S.
Next time, please put create/insert statement to generate your test case, people usually want to help, but they are lazy to prepare input data.

"C.Burke" <cc.bbuurrkkee_at_iittss.uuqq.eedduu.aauu> wrote in message news:<c8bkuo$n6g$1_at_bunyip.cc.uq.edu.au>...
> I get a
>
> *
> ERROR at line X:
> ORA-00979: not a GROUP BY expression
>
> pointing to first_value(value)
>
> Spent a few more hours of research and couldn't find any reference to
> using partition and group by together.
>
> Any further help would be appreciated.
>
>
>
> Jan wrote:
> > just add new columns into your view:
> >
> > first_value(value) over (partition by userid,trunc(date,'MON') ORDER
> > by date) fv,
> > first_value(value) over (partition by userid,trunc(date,'MON') ORDER
> > by date DESC) lv
> >
> > or
> >
> > last_value(value) over (partition by userid,trunc(date,'MON') ORDER by
> > date desc) fv,
> > last_value(value) over (partition by userid,trunc(date,'MON') ORDER by
> > date) lv
> >
> >
> > jan
> >
> > "C.Burke" <cc.bbuurrkkee_at_iittss.uuqq.eedduu.aauu> wrote in message news:<c7rrl1$9um$1_at_bunyip.cc.uq.edu.au>...
> >
> >>I have a table with userid, date and value.
> >>
> >>I currently have a view 'monthly' which aggregates these as follows:
> >>
> >>create view monthly as
> >>select userid,
> >> trunc(date,'MON') Date,
> >> avg(value) MonthlyAverage,
> >> count(value) MonthlyCount,
> >> max(value) MonthlyMaximum,
> >> min(value) MonthlyMinimum,
> >> from daily
> >>group by userid,trunc(date,'MON');
> >>
> >>Which works fine, however I also want to have two columns in the view
> >>for 'first' and 'last' (I'm going to invent the aggregates to show what
> >>I want):
> >>
> >>create view monthly as
> >>select userid,
> >> trunc(date,'MON') date,
> >> avg(value) MonthlyAverage,
> >> count(value) MonthlyCount,
> >> max(value) MonthlyMaximum,
> >> min(value) MonthlyMinimum,
> >> first(value,date) MonthlyFirstOfMonthValue,
> >> last(value,date) MonthlyLastOfMonthValue
> >> from daily
> >>group by userid,trunc(date,'MON');
> >>
> >>Effectively I want (in the view) the minimum,maximum,first and last
> >>values for the month.
> >>
> >>I've tried a subquery, but it will not co-operate because the subquery
> >>requires non-group expressions to succeed.
> >>
> >>Any hints ?
> >>
> >>Thanks
> >>
> >>PS: My e-mail address has been hubbled to protect me from spam, feel
> >>free to repair it and e-mail me, or reply here.
Received on Tue May 18 2004 - 01:08:26 CDT