SQL - sort alphabetically for strings within a column

Question

I am working with postgresql and I have a table like this:

Group | Name
======================================
1     | Mary
2     | Barry,Ann,Peter
3     | Max,Chris
4     | Richard,Mary,Peter,Oliver

The table is an example, you can consider there will be more than 10,000 different names, the max number of names in one group is 4.

I want to sort the name within each group alphabetically so the result would be like this:

Group | Name
======================================
1     | Mary
2     | Ann,Barry,Peter
3     | Chris,Max
4     | Mary,Peter,Oliver,Richard

Thanks

Normalize your data so you're not storing multiple values in a single column, and you can do this with a simple ORDER BY. If you choose to store data in a comma-separated list, expect to have to work extra hard every time you want to use it. The proper way would be to have 2 Barry, 2 Ann, 2 Peter in three separate rows, which would make the query ORDER BY Group, Name. — Ken White
– Ken White, Commented Mar 1, 2017 at 3:29

Patrick · Accepted Answer · 2017-03-01 08:19:03Z

1

SELECT t.Group, string_agg(n.names, ',' ORDER BY n.names) AS Name
FROM my_table t,
     regexp_split_to_table(t.Name, ',', 'g') n(names)
GROUP BY 1
ORDER BY 1;

edited Mar 1, 2017 at 8:19

answered Mar 1, 2017 at 6:33

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

1 Comment

Innam Hunzai Over a year ago

Its giving error ERROR: regexp_split does not support the global option SQL state: 22023

Collectives™ on Stack Overflow

SQL - sort alphabetically for strings within a column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related