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)
----+-------+-------------------------
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 по дате отправления: