Postgresql distinct issue

Question

It needs receiving unique profiles ordered by creation_date. There is following query:

SELECT DISTINCT profiles.id, COALESCE(occured_at, users_visitors.created_at, visitors.created_at) creation_date FROM "profiles" 
JOIN "visitors" ON "visitors"."profile_id" = "profiles"."id" 
LEFT JOIN events ON profiles.id = events.profile_id 
LEFT JOIN event_kinds ON event_kinds.id = events.event_kind_id 
LEFT JOIN users_visitors ON visitors.id = users_visitors.visitor_id 
WHERE (event_kinds.name = 'enter') AND "users_visitors"."user_id" = 2 
ORDER BY creation_date asc

DISTINCT ON (profiles.id) won't help once it should be used for ordering. GROUP BY profiles.id, ... doesn't work as well.

Could you help me, please?

But which creation_date value do you want if a profiles.id has several different ones? Besides, move the WHERE clause conditions to the corresponding ON clause if you reallt want LEFT JOIN's. As it is now those two execute as regular INNER JOIN's. — jarlh
– jarlh, Commented Mar 3, 2015 at 11:01

jarlh · Accepted Answer · 2015-03-03 11:05:13Z

1

Does this GROUP BY work? Or which creation_date do you want - if not the max one?

SELECT profiles.id,
       MAX(COALESCE(occured_at,
                    users_visitors.created_at,
                    visitors.created_at)) creation_date
FROM "profiles" 
  JOIN "visitors" ON "visitors"."profile_id" = "profiles"."id" 
  LEFT JOIN events ON profiles.id = events.profile_id 
  LEFT JOIN event_kinds ON event_kinds.id = events.event_kind_id
                        AND event_kinds.name = 'enter'
  LEFT JOIN users_visitors ON visitors.id = users_visitors.visitor_id
                           AND "users_visitors"."user_id" = 2
GROUP BY profiles.id
ORDER BY creation_date asc

Note how I've moved the where clause conditions to get the LEFT JOIN's to perform as LEFT JOIN's.

answered Mar 3, 2015 at 11:05

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql distinct issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related