Suppose I have a table of Foos. Each Foo has multiple Bars. I want to return all Foos and the latest Bar for each of them. How can I do this in one PostgreSQL query?

foos

id	name
1	Foo1
2	Foo2

bars

id	foo_id	created_date
1	1	2022-12-02 13:00:00
2	1	2022-12-02 13:30:00
3	2	2022-12-02 14:00:00
4	2	2022-12-02 14:30:00

Expected Result

id	name	bar.id	bar.foo_id	bar.created_date
1	Foo1	2	1	2022-12-02 13:30:00
2	Foo2	4	2	2022-12-02 14:30:00

CodePudding user response：

To find the greatest per group first we need to figure

How to define the group, here the each foo id is a group
What is the greatest value, here the greatest value is created_date

To implement this use the function like ROW_NUMBER for this one define the partition based on foo id and sorting based on created_date desc

Which in terms of code translates to:

ROW_NUMBER() OVER(partition by f.id order by b.created_date desc)

with cte as (
select 
    f.id as foo_id, 
    f.name as foo_name, 
    b.id as bar_id, 
    b.created_date,
    ROW_NUMBER() OVER(partition by f.id order by b.created_date desc) as RNUM 
from Foos as f join Bars as b on f.id = b.foo_id )
select 
    foo_id,
    foo_name,
    bar_id,
    created_date
from cte where RNUM = 1