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:
- Wrap this query into a subquery and
SELECT *
from it; thenORDER BY LOWER(countries)
would work fine. - Copy the
string_agg()
expression toORDER BY
but applyLOWER()
to it. - 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.