Re: Finding "most recent" using daterange

Поиск
Список
Период
Сортировка
От Greg Sabino Mullane
Тема Re: Finding "most recent" using daterange
Дата
Msg-id CAKAnmmLBxrQ0n4+BPb_xBQKrDRoiq4eSKdQYDbnmo7KTQVFuRg@mail.gmail.com
обсуждение исходный текст
Ответ на Finding "most recent" using daterange  (Rob Foehl <rwf@loonybin.net>)
Ответы Re: Finding "most recent" using daterange
Список pgsql-general
This is a good candidate for a window function. Also note that nulls already get sorted correctly by the DESC so no need to get 'infinity' involved, although you could write 'DESC NULLS FIRST' to be explicit about it.

with x as (select *,  row_number() over (partition by id order by upper(dates) desc, lower(dates) desc) from example)
  select id,value,dates from x where row_number = 1;

 id | value |          dates
----+-------+-------------------------
  1 | b     | [2010-01-01,)
  2 | d     | [2010-01-01,2021-01-01)
  3 | g     | [2013-01-01,)
  4 | j     | [2010-01-01,2015-01-01)
(4 rows)


Cheers,
Greg

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

Предыдущее
От: XChy
Дата:
Сообщение: Re: Missed compiler optimization issue in function select_rtable_names_for_explain
Следующее
От: Ron Johnson
Дата:
Сообщение: search_path and SET ROLE