Using the following query as simple example,

    SELECT  l.id,
            string_agg(lc.country, ' ' ORDER BY lc.country) AS countries
      FROM  licenses l
            JOIN license_countries lc ON lc.license_id = l.id
     WHERE  l.id > 80
  GROUP BY  l.id

what is the preferred way of sorting the result by the second column in a case-insensitive manner?

A straight ORDER BY LOWER(countries) is not possible, because the expressions in ORDER BY cannot refer to output column names. I can think of several ways to solve this, but I am not sure what the pros/cons are:

  1. Wrap this query into a subquery and SELECT * from it; then ORDER BY LOWER(countries) would work fine.
  2. Copy the string_agg() expression to ORDER BY but apply LOWER() to it.
  3. Add another output column - same as the second line, but with LOWER() applied - then sort by that column.

I'm currently using the first option with the original query added in a CTE (WITH query). Is my assumption correct that this would typically have the least impact on performance?

I've run into similar situations before, usually involving something like SELECT …, COUNT(*) as num_rows … ORDER BY COUNT(*) …. This is short enough, so the duplication of the expression is not as conspicuous, but it now occurs to me that this may not be clean either.

  • 1
    I typically use 1) usually with a derived table, not a CTE and sometimes 2) (auto-completion in the SQL tool helps for that)
    – user1822
    Nov 4, 2016 at 17:06


Your Answer

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