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. - Mailing list pgsql-general

From Hannes Erven
Subject 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.
Date
Msg-id 4E6F6B3E.9020702@erven.at
Whole thread Raw
In response to 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 Thompson <jreidthompson@gmail.com>)
Responses 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 Thompson <Reid.Thompson@ateb.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: 8.4.4 locked after power failure
Next
From: Grzegorz Jaśkiewicz
Date:
Subject: Re: 8.4.4 locked after power failure