Re: Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.

Reid,


> where any one of these 3
>
> 1    1       2011-01-01
> 1    1       2011-01-01
> 1    3       2011-01-01
>
> or any one of these 2
> 3    1       2011-01-05
> 3    2       2011-01-05
>
> are suitable for val = 1, val = 3 respectively.


Can you please describe in words what you are trying to accomplish? When
I look at your data and expected output, I'd say you want this:

   For each distinct value of "val1", return any value of "val2" and
   the lowest value of "date".


This is actually quite simple - you could also use max(), avg(), ...
instead of min for val2:

SELECT val1, min(val2), min(date)
FROM data
GROUP BY val1


Best regards

    -hannes