Using LENGTH(computed column) in ORDER BY in Postgres sql

Question

SELECT  concat(first_name,last_name)  AS full_name from customer
ORDER BY  length(full_name);

I tried to run this in postgre sql database. I give me this erro

[42703] ERROR: column "full_name" does not exist

How would I resolve this? Order rows by length of full name.

`order by length(concat(firstname, lastname))`? – Alan Aug 08 '20 at 02:57 — Alan, Aug 08 '20 at 02:57

score 1 · Accepted Answer · answered Aug 08 '20 at 12:52

Postgres adheres to the standard by allowing column aliases as order by keys. So this works:

SELECT  CONCAT(first_name, last_name)  AS full_name
FROM customer
ORDER BY full_name;

However, it does not extend this to expressions using the aliases. You can fix this using subqueries or CTEs. I might also suggest a lateral join:

SELECT v.full_name
FROM customer c CROSS JOIN LATERAL
     (VALUES (CONCAT(c.first_name, c.last_name))) v(full_name)
ORDER BY v.full_name;

score 0 · Answer 2 · answered Aug 08 '20 at 03:09

0

Can you please try either of the below?

SELECT  concat(first_name,last_name) COLLATE "C"  AS full_name from customer
ORDER BY  length(full_name)

OR

SELECT  concat(first_name,last_name)  from customer
ORDER BY  length(concat(first_name,last_name))

answered Aug 08 '20 at 03:09

Govind

score 0 · Answer 3 · answered Aug 08 '20 at 03:14

0

This would work, and avoids the double use of concat()

WITH results AS (
  SELECT concat(first_name, last_name) AS full_name FROM customer
)
SELECT full_name FROM results ORDER BY length(full_name)

answered Aug 08 '20 at 03:14

mike.k

3 Answers3