PostgreSQL ALIAS fails in ORDER BY clause of an SQL SELECT when using LOWER()

💬 Queries and Resources
,
1

Objective
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']

Step 1

SELECT 
  name
FROM test_names
ORDER BY LOWER(name)

This correctly returns:
alex
Helen
Tim

Step 2

SELECT 
  name as full_name
FROM test_names
ORDER BY LOWER(full_name)

This fails by returning:
image

Does retool_db support aliasing of field names in the order by clause?

2

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:

SELECT 
  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

1 Like
3

Thank you @Haseeb1399 The result of your query is a sorted list:

image

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 u.name
  FROM (
    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

Regards,