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, Mar 01 '17 at 03:29

Patrick · Answer 1 · 2017-03-01T08:19:03.803

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 01 '17 at 08:19

answered Mar 01 '17 at 06:33

Patrick

29,357
6
62
90

Its giving error ERROR: regexp_split does not support the global option SQL state: 22023 – Innam Hunzai Nov 08 '18 at 11:05

SQL - sort alphabetically for strings within a column

1 Answers1