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-12 10:02:13Z

2

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;

edited Aug 12, 2020 at 10:02

answered Aug 12, 2020 at 9:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

pratpor Over a year ago

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.

Collectives™ on Stack Overflow

Postgres distinct on with case

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related