Re: return MAX and when it happened - Mailing list pgsql-general

On Wed, Nov 19, 2008 at 08:47:57AM -0600, Scara Maccai wrote:
> 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;

Why not just do:

  SELECT * FROM mytab
  ORDER BY num, mydate
  LIMIT 1;

If you're trying to do more complicated things, DISTINCT ON may be more
useful.

> 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?

I've wanted MAX to support this sort of thing several times before, but
alas it doesn't.


  Sam