PostgreSQL One ID multiple values

Question

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?

Laurenz Albe · Accepted Answer · 2019-11-21 08:00:18Z

1

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.

answered Nov 21, 2019 at 8:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Matthew Ingle Over a year ago

Thanks Laurenz. This worked for the information i gave to you. There was a bit more info I didn't include in the original question. Your answer was correct, but I needed a slightly different solution to get what I wanted.

Matthew Ingle · Accepted Answer · 2019-11-21 18:54:27Z

0

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

answered Nov 21, 2019 at 18:54

Matthew Ingle

1194 silver badges16 bronze badges

Collectives™ on Stack Overflow

PostgreSQL One ID multiple values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related