CODEWITHSUNDEEP
postgresql
user3263892
user3263892

Reputation: 93

Postgresql sorting string_agg

I run the following query string_agg(DISTINCT grades, '|'), which executed and outputted my result in this order 01|02|03|04|05|KG|PK.

How can I rearrange it this way PK|KG|01|02|03|04|05?

SELECT
      U.CUSTOM_100000001 AS USERID
    , SC.TITLE
    , U.FIRST_NAME
    , U.LAST_NAME
    , string_AGG(DISTINCT SGL.short_name, '|')
FROM
      USERS U
    , COURSE_PERIODS CP
    , SCHOOLS SC
    , school_gradelevels SGL
WHERE
    CP.SCHOOL_ID=SC.ID
        AND
    U.STAFF_ID = CP.TEACHER_ID
        AND
    SGL.SCHOOL_ID = SC.ID
        AND
    CP.SYEAR =2015
        AND
    SGL.short_name in('PK','KG','01','02','03','04','05','06','07','08')
        AND
    SC.CUSTOM_327 IN ('0021','0025','0051','0061','0071','0073','0081','0101','0111','0131','0211','0‌​221','0294','0301','0321','0341','0361','0371','0291')
GROUP BY
    U.CUSTOM_100000001, SC.TITLE, U.FIRST_NAME, U.LAST_NAME

Upvotes: 1

Views: 2339

Answers (1)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6726

It's possible in PostgreSQL 9.0+:

SELECT
    string_agg(DISTINCT SGL.short_name
            , '|' ORDER BY
                (substring(SGL.short_name, '^[0-9]+'))::int NULLS FIRST,
                 substring(SGL.short_name, '[^0-9_]+$') DESC)
FROM school_gradelevels SGL;

Test example:

WITH tbl(grade) AS (
    VALUES
     ('01'), 
     ('02'), 
     ('03'), 
     ('PK'), 
     ('KG') 
)
SELECT grade
FROM tbl
ORDER BY (substring(grade, '^[0-9]+'))::int NULLS FIRST, substring(grade, '[^0-9_]+$') DESC;

Result:

 grade
-------
 PK
 KG
 01
 02
 03
(5 rows)

Aggregate Expressions

Upvotes: 3

Related Questions