From: | Scara Maccai <m_lists(at)yahoo(dot)it> |
---|---|
To: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: return MAX and when it happened |
Date: | 2008-11-19 16:10:08 |
Message-ID: | 49243A60.3030408@yahoo.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2008-11-19 16:28:44 | Re: return MAX and when it happened |
Previous Message | Sam Mason | 2008-11-19 15:58:32 | Re: return MAX and when it happened |