CODEWITHSUNDEEP
sqlpostgresql
MikeZ
MikeZ

Reputation: 495

Postgresql distinct issue

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?

Upvotes: 1

Views: 36

Answers (1)

jarlh
jarlh

Reputation: 44805

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.

Upvotes: 1

Related Questions