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.

Answer 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.