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

From: C.Burke <cc.bbuurrkkee_at_iittss.uuqq.eedduu.aauu>
Date: Wed, 12 May 2004 10:37:53 +1000
Message-ID: <c7rrl1$9um$1@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 11 2004 - 19:37:53 CDT