Re: using window-functions to get freshest value - how?

Поиск
Список
Период
Сортировка
От silly8888
Тема Re: using window-functions to get freshest value - how?
Дата
Msg-id 3c8f9f940911200418q63e46754oc83a0a0ba3a285@mail.gmail.com
обсуждение исходный текст
Ответ на Re: using window-functions to get freshest value - how?  ("Massa, Harald Armin" <chef@ghum.de>)
Ответы Re: using window-functions to get freshest value - how?  (Thom Brown <thombrown@gmail.com>)
Список pgsql-general
> and how would I use DISTINCT ON for this query? Please bear in mind,
> that there is more then one id_bf (just stopped the sample data with
> one of them)

I posted the answer more than hour ago:

SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf,
letztespeicherung DESC;

The equivalent with window functions would be:

SELECT * FROM (SELECT *,row_number() OVER (PARTITION BY id_rf ORDER BY
letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1

If you check the query plan you will understand why DISTINCT ON is the
best option. Essensially, DISTINCT ON has no additional cost other the
 cost of

         ORDER BY id_rf, letztespeicherung DESC

which is unavoidable

В списке pgsql-general по дате отправления:

Предыдущее
От: Sam Jas
Дата:
Сообщение: Re: Strange performance degradation
Следующее
От: Thom Brown
Дата:
Сообщение: Re: using window-functions to get freshest value - how?