CODEWITHSUNDEEP
postgresql
Matthew Ingle
Matthew Ingle

Reputation: 119

PostgreSQL One ID multiple values

I have a Postgres table where one id may have multiple Channel values as follows

ID   |Channel | Column 3 | Column 4
_____|________|__________|_________
1    | Sports | x        | null
1    | Organic| x        | z
2    | Organic| null     | q
3    | Arts   | b        | w
3    | Organic| e        | r
4    | Sports | sp       | t

No ID will have a duplicate channel name, and no ID will be both Sports and Arts. That is, ID 1 could have a Sports and Organic channel, a Sports and Arts channel, but not two sports or two organic entries and not a Sports and Arts channel. I want all IDs to be in the query, but if there is a non-organic channel I prefer that. The result I would want would be

ID   |Channel | Column 3 | Column 4
_____|________|__________|_________
1    | Sports | x        | null
2    | Organic| null     | q
3    | Arts   | b        | w
4    | Sports | sp       | t

I feel like there is some CTE here, a rank and partition or something that could do the trick, but I'm just not getting it. I'm only including Columns 3 and 4 to show there are extra columns.

Does anyone have any ideas on the code to deploy here?

Upvotes: 0

Views: 414

Answers (2)

Matthew Ingle
Matthew Ingle

Reputation: 119

I ended up using a rank over function ROW_NUMBER () over (partition by salesforce_id order by case when channel is organic then 0 else 1 end desc, timestamp desc) as id_rank

I didn't include in the original question that I had a timestamp! This works now. Thanks

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246533

You could use DISTINCT ON with an appropriate ORDER BY clause:

SELECT DISTINCT ON (id)
       id, channel, column3, column4
FROM atable
ORDER BY id, channel = 'Organic';

This relies on the fact that FALSE < TRUE.

Upvotes: 1

Related Questions