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?
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; then
ORDER BY LOWER(countries)would work fine.
- Copy the
ORDER BYbut apply
- 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.