Non-scalar correlated subqueries in select?

1k views Asked by At

Is it possible to have non-scalar correlated subqueries in the select? Similar to how generate_series can be used in select?

By non-scalar I mean returning more than 1 row (not really concerned with columns at the moment):

select
  o1.user_id,
  o1.order_id,
  o1.order_date,
  (select
     o2.order_id
   from orders o2
   where o2.user_id = o1.user_id
     and o2.order_date < o1.order_date
   order by o2.order_date desc
   limit 2) as last_2_orders
from orders o1

I was told that anything that can be expressed as a lateral join can be expressed as a correlated subquery. So I'm trying to find a correlated subquery that can express the following:

select
  o1.user_id,
  o1.order_id,
  o1.order_date,
  o3.last_2_orders
from orders o1
left join lateral (
  select
    o2.order_id as last_2_orders
  from orders o2
  where o2.user_id = o1.user_id
    and o2.order_date < o1.order_date
  order by o2.order_date desc
  limit 2
) as o3 on true

EDIT: This kind of works but it doesn't satisfy the left outer requirement:

select
  o1.user_id,
  o1.order_id,
  o1.order_date,
  unnest(
    array(
      select o2.order_id
      from orders o2
      where o2.user_id = o1.user_id
        and o2.order_date < o1.order_date
      order by o2.order_date desc
      limit 2
    )
  ) as last_2_orders
from orders o1
0

There are 0 answers