How to use DISTINCT ON but ORDER BY another expression?

Question

The model Subscription has_many SubscriptionCart.

A SubscriptionCart has a status and an authorized_at date.

I need to pick the cart with the oldest authorized_at date from all the carts associated to a Subscription, and then I have to order all the returned Subscription results by this subscription_carts.authorized_at column.

The query below is working but I can't figure out how to select DISTINCT ON subscription.id to avoid duplicates but ORDER BY subscription_carts.authorized_at .

raw sql query so far:

select distinct on (s.id) s.id as subscription_id, subscription_carts.authorized_at, s.*
from subscriptions s
join subscription_carts subscription_carts on subscription_carts.subscription_id = s.id 
and subscription_carts.plan_id = s.plan_id
where subscription_carts.status = 'processed'
and s.status IN ('authorized','in_trial', 'paused')
order by s.id, subscription_carts.authorized_at

If I try to ORDER BY subscription_carts.authorized_at first, I get an error because the DISTINCT ON and ORDER BY expressions must be in the same order.

The solutions I've found seem too complicated for what I need and I've failed to implement them because I don't understand them fully.

Would it be better to GROUP BY subscription_id and then pick from that group instead of using DISTINCT ON? Any help appreciated.

Answer 1

This requirement is necessary to make DISTINCT ON work; to change the final order, you can add an outer query with another ORDER BY clause:

SELECT *
FROM (SELECT DISTINCT ON (s.id)
             s.id as subscription_id, subscription_carts.authorized_at, s.*
      FROM subscriptions s
         JOIN ...
      WHERE ...
      ORDER BY s.id, subscription_carts.authorized_at
     ) AS subq
ORDER BY authorized_at;