Re: (Hopefully stupid) select question.

On 01/24/2011 04:56 PM, Tom Lane wrote:
> Fredric Fredricson<Fredric(dot)Fredricson(at)bonetmail(dot)com> writes:
>> ... Now I want the latest "someData" for each "ref" like:
>> The best solution I could find depended on the fact that serial is
>> higher for higher dates. I do not like that because if that is true, it
>> is an indirect way to get the data and could possibly, in the future,
>> yield the wrong result if unrelated changes where made or id's reused.
>> Here is my solution (that depend on the SERIAL):
>> SELECT x.ref,x.someData
>> FROM t as x
>> NATURAL JOIN (SELECT ref,max(id) AS id FROM t GROUP BY ref ORDER BY
>> ref) AS y ;
> Well, you could just substitute max(inserted) for max(id). But you
I tried this, but that did not get me "someData" because I need "id" for
that.
> should also consider using DISTINCT ON --- look at the "weather reports"
> example in the SELECT reference page.
DISTINCT ON did the trick. Thank you!
My select is now much simpler:
SELECT DISTINCT ON (ref) ref, someData FROM t ORDER BY red,date DESC;

Also 20-30% faster in my setup.

I tried DISTINCT but I wasn't aware of the "DISTINCT ON" functionality.
You live - you learn.
> BTW, "inserted DATE DEFAULT CURRENT_TIMESTAMP" looks pretty fishy.
> You sure the column type shouldn't be timestamp or timestamptz, to
> support multiple updates per day?
Sorry, my typo, it is really a TIMESTAMP(0), of course.

/Fredric
> regards, tom lane
>