How to sort a converted field using string_agg

Question

How do I sort a converted field. There are similar questions, however I dont know why its not working by me

-- the field 'number' is an integer

string_agg(distinct cast(number as varchar(4)) , ', ')

however the results return in very strange random order

Results
12,11,10,1,29

I simply wanted it sorted normally 1,10,11,12,29

I tried this code based on the available posts but it didnt work and received the following error

string_agg(
    distinct cast(number as varchar(4)),', ' order by number) 
    as SPJ

--ERROR: in an aggregate with distinct order by expressions must appear in argument list  

So tried this too and not the answer I want

string_agg(
    distinct cast(number as varchar(4)) , ', ' order by
                            cast(number as varchar(4))) as SPJ
results 
10,11,8,9

What am I doing wrong?

Answer 1

This is all complicated because of the types -- strings are not ordered the same way as numbers. So, you can do this using arrays instead:

with t as (
      select num
      from (values (1), (10), (11), (12), (29), (2)) v(num)
     )
select array_to_string(array_agg(distinct num order by num), ', ')
from t ;