0

I need some help to create a function to order:

  • first the element inside an array;
  • second order by the column;

I will try to explain through an example:

id |   names    |
- - - - - - - - -
1  |{ad, ab, cd}|
2  |{aa, ac}    |
3  |{cd, da}    |

The result of ORDER BY names should be 

id |   names     |
- - - - - - - - -
2  | {aa, ac}    |
1  | {ab, ad, cd}|
3  | {cd, da}    |

I already tried this solution - Sorting array elements - but it doesn't work for me.

UPDATE: I think I get a point. I sorted inside the array but I don't change the order - I need an update on that. Probably I have to create a kind of virtual array (I don't know how to do that) and store the value which I sorted.

liviubiur
  • 111
  • 3
  • 13
  • What exactly "didn't work"? Did you create the sort function suggested in the linked question? –  Mar 26 '19 at 14:11
  • Yes, I created this function 'CREATE OR REPLACE FUNCTION array_sort (ANYARRAY) RETURNS ANYARRAY LANGUAGE SQL AS $$ SELECT ARRAY(SELECT unnest($1) ORDER BY 1) $$;' but it didn't sorting the array in the correct way. The elements inside the array is sorting random and not in ASC or DESC way. – liviubiur Mar 26 '19 at 14:11
  • Please show: your Postgres version (*always!*), your table definition (`CREATE TABLE` statement), min/avg/max array length, cardinalities, what you tried. – Erwin Brandstetter Mar 26 '19 at 15:33

0 Answers0