Re: return MAX and when it happened

On Wed, Nov 19, 2008 at 09:35:34AM -0600, Scara Maccai wrote:
> Well you guys are right; the problem is that the query is actually more
> complex, I tried to simplify it for the question, but ended up with a
> trivial question...

always a problem with simplification, I've done the same far too often
myself! at least you tried :)

> Let's see if this example is better:
>
> CREATE TABLE mytab
> (
> num integer,
> num1 integer,
> num2 integer,
> mydate timestamp
> );
>
>
> select MAX(num), SUM(num1)+SUM(num2), date_trunc('day', mydate) from
> mytab group by date_trunc('day', mydate)
>
> but I also want the timestamp where MAX(num) happened for each day.

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;

Sam