Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Oracle/SQL - Doing a 'first' or 'last' in an SQL View/Query aggregate
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,
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
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
![]() |
![]() |