SELECT email, org_id, MIN(created_at) as first_user_created
FROM users
WHERE org_id IN (SELECT org_id FROM subscriptions)
GROUP BY email, org_id;
Result of the above query gives me multiple user records per org_id.
What I want: Per organization - return the email
, org_id
and first_user_created
of user that got created the earliest.
CodePudding user response:
One solution uses DISTINCT ON
:
SELECT DISTINCT ON (u.org_id) u.email, u.org_id, u.created_at
FROM users u
INNER JOIN subscriptions s
ON s.org_id = u.org_id
ORDER BY u.org_id, u.created_at;
Another way to do this uses RANK()
:
WITH cte AS (
SELECT u.email, u.org_id, u.created_at,
RANK() OVER (PARTITION BY u.org_id ORDER BY u.created_at) rnk
FROM users u
INNER JOIN subscriptions s ON s.org_id = u.org_id
)
SELECT email, org_id, created_at
FROM cte
WHERE rnk = 1;