I'm trying to get make a retool query that will do a case insensitive sort on the ORDER BY clause of a SELECT statement.
The data is in retool_db (Postgresql). The table has an integer id and a "name" column which is text, unique, not nullable, and no default. ['alex','Helen','Tim']
ORDER BY LOWER(name)
This correctly returns:
name as full_name
ORDER BY LOWER(full_name)
This fails by returning:
Does retool_db support aliasing of field names in the order by clause?
I might be wrong, but I think the SQL parsing/query-plan re-orders the LOWER statement to happen before you actually asign an alias. Hence full-name doesn't exist yet when the function is called. The following query should work:
LOWER(u.name) as full_name
FROM users u
ORDER BY full_name
Here, you apply LOWER to the names first (a column that does exist) and then you can order by the alias. @stewart.anstey
Thank you @Haseeb1399 The result of your query is a sorted list:
But the names are in lower case. My alias comes from a CASE statement with CONCAT() and I really need to retain the formatting while doing the sort.
Turns out the answer (StackOverflow) is:
SELECT name, CONCAT('something here', LOWER(name)) AS full_name
FROM users) u
ORDER BY LOWER(u.full_name)
Which works because:
- The CONCAT is simply the thing that breaks the ordering of the items
- The alias full_name is passed up to the parent SELECT
- full_name is now available to the ORDER BY as it is considered a proper field name