Re: Best way to get the latest revision from a table

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Nikolas Everett" <nik9000(at)gmail(dot)com>,<sthomas(at)peak6(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Best way to get the latest revision from a table
Date: 2011-01-14 23:33:27
Message-ID: 4D3088E70200002500039632@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Shaun Thomas <sthomas(at)peak6(dot)com> wrote:

> This actually looks like a perfect candidate for DISTINCT ON.
>
> SELECT DISTINCT ON (a, b) a, b, revision
> FROM test
> ORDER BY a, b DESC;

I wouldn't say perfect. It runs about eight times slower than what
I suggested and returns a fairly random value for revision instead
of the max(revision).

-Kevin

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-01-14 23:40:43 Re: Best way to get the latest revision from a table
Previous Message Shaun Thomas 2011-01-14 23:06:48 Re: Best way to get the latest revision from a table