Postgres distinct on with case

Question

I have a query where I am using CASE statement inside DISTINCT ON like this:

SELECT
    DISTINCT ON(COALESCE(users.cuid,events.uid),
        CASE WHEN events.props->>'prop' IS NULL THEN '$none' WHEN events.props->>'prop' = '' THEN '$none' ELSE events.props->>'prop' END)
    COALESCE(users.cuid, events.uid) as coal_user_id,
    CASE WHEN events.props->>'prop' IS NULL THEN '$none' WHEN events.props->>'prop' = '' THEN '$none' ELSE events.props->>'prop' END AS _group_key_0, events.uid as event_uid
FROM events JOIN users ON events.uid=users.id 
WHERE events.project_id='<>' AND timestamp>='<>' AND timestamp<='<>'
ORDER BY coal_user_id, _group_key_0, events.timestamp ASC

On running, I am getting this error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

I tried using alias like as _group_key_0 inside distinct on clause, but that again was throwing error a different error.

Is there a way to use CASE statements in distinct on and make this work?

Laurenz Albe · Accepted Answer · 2020-08-12T10:02:13.553

You have to add the expressions from the DISTINCT ON clause at the beginning of the ORDER BY clause, like the error message says:

SELECT DISTINCT ON(COALESCE(users.cuid,events.uid),
                   CASE WHEN events.props->>'prop' IS NULL THEN '$none'
                        WHEN events.props->>'prop' = '' THEN '$none'
                        ELSE events.props->>'prop'
                   END)
       COALESCE(users.cuid, events.uid) as coal_user_id,
       CASE WHEN events.props->>'prop' IS NULL THEN '$none'
            WHEN events.props->>'prop' = '' THEN '$none'
            ELSE events.props->>'prop'
       END AS _group_key_0,
       events.uid as event_uid
FROM events JOIN users ON events.uid=users.id 
WHERE events.project_id='<>'
  AND timestamp>='<>'
  AND timestamp<='<>'
ORDER BY COALESCE(users.cuid,events.uid),
         CASE WHEN events.props->>'prop' IS NULL THEN '$none'
              WHEN events.props->>'prop' = '' THEN '$none'
              ELSE events.props->>'prop'
         END,
         events.timestamp ASC;

Writing complete CASE in ORDER BY worked actually. While experimenting, for `coal_user_id`, just writing `ORDER BY coal_user_id` was working so I was expecting it to work with `_group_key_0` as well in similar way. — pratpor, Aug 12 '20 at 09:44

Postgres distinct on with case

1 Answers1