CODEWITHSUNDEEP
postgresql
techie11
techie11

Reputation: 1387

select values of a column based on values of another column

I have a table:

ID,     color
----    ------
1       red
2       red
3       green
4       green
5       green
6       blue
7       blue

Desired result of the query:

red    1,2
green  3,4,5
blue   6,7

how to write the query in Postgres SQL?

Upvotes: 0

Views: 1296

Answers (1)

Belayer
Belayer

Reputation: 14861

What you are looking for is the array_agg with the optional order by. In the following the inner select builds the list of ordered id for each color, the outer query then orders the overall result by the lowest id value within the colors.

with test( id, color) as 
     ( values (1, 'red')
            , (2, 'red')
            , (3, 'green')
            , (4, 'green')
            , (5, 'green')
            , (6, 'blue')
            , (7, 'blue')
      ) 

 select color, arr as id_list
   from (
          select color, array_agg(id order by id) arr
            from test 
           group by color
        ) sq    
 order by arr[1];

Upvotes: 1

Related Questions