SQL - pull newest 1 record for given column

Question

I have the following query, which works fine to to pull information on cooks, their menus and the food items they sell.

However, it is missing one final column, "last_order_placed"

This column needs to pull the single latest (most recent) entry from the table "orders", column name "placed". each specific store (store.id) will have a value in orders.placed each time that food item sells.

This is a postgresql 13 database.

I'd like to add a column to include the newest entry from orders.placed for each store (store.id):

select distinct
account.id,
account.email,
account.firstname, 
account.lastname, 
account.phone, 
account.zip_code,
menu.name,
kitchen_item.name, 
kitchen_item.price,
kitchen_item.daily_max_orders,
menu.pickup_start_time, 
menu.pickup_end_time,
menu.repeat_mon,
menu.repeat_tues,
menu.repeat_wed,
menu.repeat_thurs,
menu.repeat_fri,
menu.repeat_sat, 
menu.repeat_sun,
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join kitchen_item on store.id = kitchen_item.store_id
left join menu_item on (kitchen_item.id = menu_item.kitchen_item_id and menu.id = menu_item.menu_id)
left join orders on store.id = orders.store_id
where account.is_seller_fully_registered = True
and account.is_deleted = False
and menu.deleted = False
and menu_item.is_deleted = False
group by account.id, account.email, account.firstname, account.lastname, account.phone, account.zip_code, menu.name, kitchen_item.name, kitchen_item.price, kitchen_item.daily_max_orders, menu.pickup_start_time, menu.pickup_end_time, menu.repeat_mon, menu.repeat_tues, menu.repeat_wed, menu.repeat_thurs, menu.repeat_fri, menu.repeat_sat, menu.repeat_sun
order by account.id asc;

I am at a loss for how to incorporate this with all of the other joins. I have tried suggestions from other posts, and from what I gather I should be implementing this on the join for the orders table. Something like:

INNER JOIN (
             select MAX (orders.placed) last_order
             from orders o on orders.store_id = store.id
          )
        GROUP BY store.id

Can anybody help me out?

Answer 1

If I understand correctly, you can use distinct on:

select distinct on (store_id) . . .
. . .
order by store_id, o.placed desc

You probably don't need the group by. At least, your question does not explain why it would be necessary.