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

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 Fri May 14 2004 - 04:03:14 CDT