Re: return MAX and when it happened

On Wed, Nov 19, 2008 at 10:10:08AM -0600, Scara Maccai wrote:
> Sam Mason wrote:
> >Do you really want the SUM of num1 and num2, because that makes it more
> >fiddly and would be where having MAX accept a record/tuple would be
> >best.  If you don't, maybe something like:
> >
> >  SELECT DISTINCT ON (date_trunc('day', mydate))
> >      date_trunc('day', mydate), num, num1+num2
> >    FROM mytab
> >    ORDER BY date_trunc('day', mydate), num DESC;
> >  
> Unfortunately, I need:
> 
> - the SUM of some values, grouped per day
> - the MAX of some other value, grouped per day
> - the timestamp where the MAX above happened (per day, of course)
> 
> The "num" columns are events, and sometimes I need to know not only the 
> amount of a certain event per day, but also WHEN the MAX of some event 
> happened...
> 
> I guess I have to use a Composite Type (something like "numeric, 
> timestamp") + user defined aggregate?

Yes, that sounds about right. It's all going to be a bit more of a
fiddle than it should be, but at least it's possible.  An alternative
would be to use a couple of sub-queries and put things back together
again after getting each piece of data.  The custom aggregate sounds the
most elegant, it's just annoying that it's so much fiddling to get it
all working to start with.


  Sam