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

From: C.Burke <cc.bbuurrkkee_at_iittss.uuqq.eedduu.aauu>
Date: Tue, 18 May 2004 10:21:47 +1000
Message-ID: <c8bkuo$n6g$1@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 Mon May 17 2004 - 19:21:47 CDT