How to Sort the Result from String_Agg()

How to sort the result from string_agg()

With postgres 9.0+ you can write:

select string_agg(product,' | ' order by product) from "tblproducts"

Details here.

I can't figure out how to Order by with string_agg

Use WITHIN GROUP (ORDER BY ...):

SELECT
    ID,            
    STRING_AGG(​TRY_CONVERT(varchar, Date, 101) + ': ' + Notes +
               CHAR(13) + CHAR(10) + CHAR(13), CHAR(10))
        WITHIN GROUP (ORDER BY Date DESC) AS Expr1​
FROM
(
    SELECT DISTINCT ac4.ID, nd.Notes, nd.Date
    FROM dbo.ReleaseTrackerNotes AS nd 
    INNER JOIN dbo.ReleaseTracker AS ac4
        ON ac4.ID = nd.ReleaseTrackerID
) AS vNotes
GROUP BY ID;

In postgres, how to sort on, among others, a result value of a string_agg function, without selecting that field? Subquery not possible

ORDER BY can work on calculated expressions too; it doesn't have to be that you calculate something in the SELECT that you then alias and reference the alias in the ORDER BY

Take a look at doing:

SELECT
    e.id
FROM 
  ...
ORDER BY 
    s.name ASC,
    string_agg(csb.description, ',' ORDER BY cs.id ASC) ASC, 
    event_level DESC
LIMIT 20 OFFSET 0

Also double check that left join you have there in the middle of the inner joins; any nulls it produces will be removed again when the next table is inner joined into it, so you can either inner join it or if you're losing data adopt a pattern of:

w
JOIN x
LEFT JOIN (
  y
  JOIN z
) a

ie don't left join y to x then inner join z to y, inner join y and z first then left join the result onto x

How to get strings comma separated in ascending order with STRING_AGG()

You can use within group syntax

SELECT Color
    , Count(*) AS Count
    , STRING_AGG([Order],',') WITHIN GROUP (ORDER BY [Order]) AS AggOrder
FROM MyTable
GROUP BY Color

order by in string_agg does not seems to work

Your line_no is varchar, as you can typically notice

'1' < '14' < '16 < '17' < '2'

So, just simply parse the varchar into int solve the problem.

select recid, 
STRING_AGG(DefaultDimension, '-')  WITHIN GROUP (ORDER BY CAST(line_no AS int) ASC) DefaultDimension,
STRING_AGG(DefaultDimensionName, '-') WITHIN GROUP (ORDER BY CAST(line_no AS int)ASC) DefaultDimensionName 
from #tmp
group by recid

SQL Server 2017 STRING_AGG Order By

I can repro this on SQL Server 2019 build 15.0.4198.2 too.

It just requires the query to be run in the context of a database set to COMPATIBILITY_LEVEL of 100 (2008).

All other (later) compatibility levels work.

The documentation states

STRING_AGG is available in any compatibility level.

And doesn't mention this.

If you are unable to change the compatibility level of the database to something higher then possibly you will need to fall back to the old XML PATH method of concatenating ordered results.

use distinct and order by in STRING_AGG function

The error message is quite clear. The expression that you use in the ORDER BY clause must also appear in the aggregated part.

You could do:

SELECT STRING_AGG(DISTINCT foo.a::TEXT, ',' ORDER BY foo.a::TEXT DESC)
FROM (
    SELECT 1 As a
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 2
) AS foo

Demo on DB Fiddle

While this will work, the problem with this solution is that it will order numbers as strings, that do not have the same ordering rules. String wise, 10 is less than 2.

Another option is to use arrays: first, ARRAY_AGG() can be used to aggregate the numbers (with proper, numeric ordering), then you can turn it to a comma-separated list of strings with ARRAY_TO_STRING().

SELECT ARRAY_TO_STRING(ARRAY_AGG(DISTINCT a ORDER BY a DESC), ',')
FROM (
    SELECT 1 As a
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 2
) AS foo

Demo on DB Fiddle

PostgreSQL: Getting STRING_AGG to respect the ordering from a CTE

The ORDER BY is meaningless in the CTE. You can ORDER BY as part of the STRING_AGG():

SELECT staff_id,
       STRING_AGG(CONCAT(team, ' until ' || most_recent_date_left),
                  CHR(10)
                  ORDER BY most_recent_date_left DESC
                 ) AS "teams"
FROM most_recent_leave_dates
GROUP BY staff_id;

Order string_agg over partition | BigQuery

BQ Documentation specifically says that OVER clauses (aka analytic functions) is incompatible with other clauses within STRING_AGG().

I think your best bet is to order before the aggregation. Use CTEs to organize and order your data, then apply STRING_AGG() at the end.

Sort conditional data in Postgres STRING_AGG

You don't require string_agg(). You could instead do:

SELECT STRING_AGG(distinct name, ', ' ORDER BY name) names,
       CONCAT_WS(',',
                 (CASE WHEN SUM( (something = true)::int ) > 0 THEN 'type1'),
                 (CASE WHEN SUM( (not (something = true) )::int ) > 0 THEN 'type2')
                ) as types
FROM organisations o;

You may have oversimplified the query, but for what you provided, you don't need string_agg(distinct) for the second portion.

---

Related Topics