Re: return MAX and when it happened

Hi Scara,

This should work just fine:

select num, min(mydate) from mytab group by num order by num desc limit
1;

If you have an index on 'num' it will also be fast.

Cheers,
Csaba.

On Wed, 2008-11-19 at 08:47 -0600, Scara Maccai wrote:
> Hi all,
>
> suppose I have a table like:
>
> CREATE TABLE mytab
> (
> num integer,
> mydate timestamp
> );
>
> and I want to find MAX(num) and the "mydate" where it first happened.
>
> I guess I could use
>
> select * from mytab where num = (select MAX(num) from mytab) order by
> mydate limit 1;
>
> but that would scan the data twice (I guess...)
>
> Do I have to write my own MAX function, something like:
>
> select MYMAX(num, timestamp) from mytab
>
> which would return a custom type?
> Or is there a better way?
>
>
>
>
>
>