0

I obtained a list of numbers (and text) by a string_agg.

For instance, that :

"09,12,20,200,217,400,500,90,91,92,Exp3"

This is the string_agg:

string_agg(DISTINCT t_ligne.ligne_code::text, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes

My trouble with the ordering: I need an alphanumeric order like:

"09,12,20,90,91,92,200,217,400,500,Exp3"

If I try a CAST, it refuses to accept text "numbers" like "Exp3".

So I didn't find any way to order it like values with text objects at the end.

EDIT I don't know how to use WITH in my query without cutting my results! In fact, this is the total query:

SELECT l_arret_ligne.idap,
       t_arret.nom_arret AS nom,
       string_agg(DISTINCT t_ligne.ligne_code::text, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes
FROM l_arret_ligne,
     t_ligne,
     t_arret
WHERE l_arret_ligne.id_ligne::text = t_ligne.id_ligne::text 
  AND l_arret_ligne.idap::text = t_arret.idap::text 
  AND t_ligne.ligne_type::text <> 'Scolaire'::text
GROUP BY l_arret_ligne.idap, t_arret.nom_arret

As my string_agg is inside a global query, I don't understand how it would be possible to cut it with WITH and globalize it at the end.

Maybe do I understand bad...

Thanks again!

EDIT 2: Hello again! I found one very good solution to order my results:

SELECT string_agg("rla"."t_ligne_19032019"."ligne_code", ',' ORDER BY case when (UPPER(ligne_code)=LOWER(ligne_code))=true then substring(concat('000000000000000',ligne_code),length(ligne_code)+1,15) else ligne_code end) AS lignes
FROM "rla"."t_ligne_19032019"

It works very well. BUT always the same trouble with DISTINCT! If I add DISTINCT in the string_agg, same message:

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

Any solution? Any advice?

Thanks again!

Cyril

11
  • I need an alphanumeric order like It is NOT A-order. Does you need something like "first numbers ordered by value, then A-ordered non-numbers" ? Build proper expression using CASE statement.
    – Akina
    Mar 15, 2019 at 8:55
  • 1
    For example, simply add zero-padding to some fixed char length, if the value is numeric one...
    – Akina
    Mar 15, 2019 at 9:03
  • Thanks. CASE is a trouble, because I can not use ORDER BY CASE with DISTINCT, pgsql does not accept.
    – Cy's
    Mar 15, 2019 at 11:08
  • I can not use ORDER BY CASE with DISTINCT Move the query to WITH, and order its result in outer query.
    – Akina
    Mar 15, 2019 at 11:20
  • Well… I guess this should help me… But I do not see how to do such a thing… Can you be more precise? Sorry but I am not an expert...
    – Cy's
    Mar 15, 2019 at 12:11

1 Answer 1

1

The order by in a string_agg() can absolutely use a CASE expression:

with data (ligne_code) as (
  values 
  ('a value'), ('09'), ('12'), ('20'), ('007'), 
  ('200'), ('92'), ('11'), ('Foo'), ('Exp3')
) 
select string_agg(ligne_code, ',' order by case when ligne_code ~'^[0-9]+$' then ligne_code::int end, ligne_code)
from data;

returns:

007,09,11,12,20,92,200,a value,Exp3,Foo

The expression when ligne_code ~'^[0-9]+$' checks if ligne_code consists of numbers. If that is the case it's converted to an integer which is the first sort criteria. All non-numbers come after that and are sorted in a alphabetic way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.