Selecting rows based on distinct value of one column (per group) in Postgres

Question

I have following table in PostgreSQL 11.

col1    col2                          col3          col4
3876    Dexamethasone                 Dexamethasone A01AC
3876    Dexamethasone                 Dexamethasone C05AA
3876    Dexamethasone                 Dexamethasone D07AB
3924    Dexamethasone                 Dexamethasone A01AD
3924    Dexamethasone                 Dexamethasone C05AB
3925    Dexamethasone sulphate        Dexamethasone A01AC
3925    Dexamethasone sulphate        Dexamethasone C05AA

I would like to get rows with distinct values of col1, col2, col3 and aggregate col4 and take the first value of col1 if the col1, col2, col3 are identical.

The desired output is:

col1    col2                    col3            col4
3876    Dexamethasone           Dexamethasone   A01AC | C05AA | D07AB | A01AD | C05AB
3925    Dexamethasone sulphate  Dexamethasone   A01AC | C05AA

I tried following query.

select distinct on (col1, col2, col3) 
        col1, 
        col2, 
        col3, 
        string_agg(col4, ',') 
from table
where col2 ilike '%dexamethasone%' and col1 = 'Dexamethasone'
group by col1, col2, col3;

How can I limit the output to get one col1 value per col2, col3.. for eg. selecting col1 value: 3876 and excluding 3924.

What happened to the records with col1 = 3924? Shouldn't there be an entry for this combination of values as well, and if not, then why not? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 10, 2020 at 6:59
distinct on () doesn't really make sense when used with the same columns in the group by — user330315
– user330315, Commented Jul 10, 2020 at 7:02

SABER · Accepted Answer · 2020-07-10 07:15:16Z

1

You can try something like this

select min(col1) as col1 , 
                    col2, 
                    col3, 
                    string_agg(col4, ',') 
               from table 
               where col2 like '%dexamethasone%' 
                        and col1 = 'Dexamethasone' 
               group by col2, col3;

try reading group by clause in postgres.

answered Jul 10, 2020 at 7:15

SABER

4737 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Selecting rows based on distinct value of one column (per group) in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related