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

От: Tom Lane
Тема: Re: Best way to get the latest revision from a table
Дата: ,
Msg-id: 14113.1295048443@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Best way to get the latest revision from a table  ("Kevin Grittner")
Ответы: Re: Best way to get the latest revision from a table  ("Kevin Grittner")
Re: Best way to get the latest revision from a table  (Shaun Thomas)
Список: pgsql-performance

Скрыть дерево обсуждения

Best way to get the latest revision from a table  (Nikolas Everett, )
 Re: Best way to get the latest revision from a table  ("Kevin Grittner", )
  Re: Best way to get the latest revision from a table  (Nikolas Everett, )
   Re: Best way to get the latest revision from a table  ("Kevin Grittner", )
 Re: Best way to get the latest revision from a table  (Shaun Thomas, )
  Re: Best way to get the latest revision from a table  ("Kevin Grittner", )
   Re: Best way to get the latest revision from a table  (Tom Lane, )
    Re: Best way to get the latest revision from a table  ("Kevin Grittner", )
     Re: Best way to get the latest revision from a table  (Nikolas Everett, )
      Re: Best way to get the latest revision from a table  (Shaun Thomas, )
      Re: Best way to get the latest revision from a table  (Robert Haas, )
       Re: Best way to get the latest revision from a table  (Nikolas Everett, )
    Re: Best way to get the latest revision from a table  (Shaun Thomas, )

"Kevin Grittner" <> writes:
> Shaun Thomas <> 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).

Shaun's example is a bit off: normally, when using DISTINCT ON, you want
an ORDER BY key that uses all the given DISTINCT keys and then some
more.  To get the max revision for each a/b combination it ought to be

SELECT DISTINCT ON (a, b) a, b, revision
   FROM test
   ORDER BY a, b, revision DESC;

As for speed, either one might be faster in a particular situation.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Nikolas Everett
Дата:
Сообщение: Re: Best way to get the latest revision from a table
От: Craig Ringer
Дата:
Сообщение: Re: The good, old times