Postgresql SELECT ORDER BY Column generated by WITH

I have SELECT query on Postgre that contain WITH subquery as json column. I need to order query result based on WITH subquery json data or column.

This is a sample query that I ran:

SELECT 
    person.id,
    ( WITH person_warehouse AS (
             SELECT count(warehouse.id) AS total_warehouse,
                    sum((warehouse.detailinfo -> 'total_area'::text)::numeric) AS total_area_storage
             FROM warehouse
             WHERE warehouse.owner_id = person.id
             GROUP BY warehouse.owner_id
            )
     SELECT row_to_json(person_warehouse .*) AS json_tree
       FROM person_warehouse) AS warehousedata,
FROM 
  person
ORDER BY warehousedata->>'total_warehouse' DESC

But it is return that warehousedata is not a column. How can I sort select query based on subquery result?

Thank You

1 answer

  • answered 2019-10-08 06:08 Laurenz Albe

    You can reference an alias in the ORDER BY clause, but you cannot use that alias in an expression. You'd have to repeat the expression from the SELECT list.

    This query is written in a spectacularly complicated fashion, it could be written much simpler:

    SELECT person.id,
           json_build_object(
              'total_warehouse',
              count(warehouse.id),
              'total_area_storage',
              sum((warehouse.detailinfo ->> 'total_area')::numeric)
           ) AS warehousedata
    FROM person
       LEFT JOIN warehouse
          ON warehouse.owner_id = person.id
    GROUP BY person_id
    ORDER BY count(warehouse.id) DESC;
    

    If you really want the ORDER BY clause sorted as a string, throw in a type cast.