“Column does not exist” with INNER JOIN and MAX query in ORDER BY I'm using PostgreSQL 9.6 with ActiveRecord. I'm trying to get all customers sorted by a specific provider. The relationship between models is: customer has many orders, which belong to a provider.

What I want to achieve is something like:

Example data:

customer1 &lt;- order1 -&gt; provider1
customer2 &lt;- order2 -&gt; provider2
customer3 &lt;- order3 -&gt; provider3


Expecting result:

query_for_provider(2) =&gt; customer2, customer1, customer3 
query_for_provider(1) =&gt; customer1, customer2, customer3 
query_for_provider(3) =&gt; customer3, customer1, customer2 


I wrote the following query:

SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
GROUP BY customers.id
ORDER BY @(2 - provider_id);


Unfortunately, it returns an error:


column "provider_id" doesn't exist



When I wrap this with SELECT * FROM (as below) it works, but in this case, can't use this with ActiveRecord, which requires SELECT ... FROM customers construction.

SELECT * FROM (
  SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
  INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
  INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
  GROUP BY customers.id
) AS customers_with_provider
ORDER BY @(2 - provider_id);


Any thoughts how to achieve this?
To sort a given provider_id first, this is cheaper:

SELECT c.*, MAX(o.provider_id) AS provider_id
FROM   customers c
JOIN   orders    o ON o.patient_id = c.id
GROUP  BY c.id
ORDER  BY MAX(o.provider_id) &lt;&gt; 2        -- to list provider_id 2 first
        , MAX(o.provider_id);            -- to sort the rest ascending


Works because FALSE sorts before TRUE.
Add provider_id as 2nd ORDER BY item to sort the rest in ascending order.
(NULL sorts last in ascending order in any case.)

Note that your original expression @(2 - provider_id) also affects the sort of the rest since it folds values above and below the given provider_id. Makes it impossible to sort the rest in ascending order like you requested - and is therefore wrong on principal.

Assuming that referential integrity is enforced by a FOREIGN KEY constraint to providers (the typical setup), you don't need to include that table in the query at all. Faster.

Related:


Select rows for which at least one row per set meets a condition
Sorting null values after all others, except special


If that involves most or all of table orders it's faster to aggregate first and join later:

SELECT c.*, o.provider_id
FROM   customers c
JOIN  (
   SELECT patient_id, MAX(provider_id) AS provider_id
   FROM   orders
   GROUP  BY 1
   ) o ON o.patient_id = c.id
ORDER  BY o.provider_id &lt;&gt; 2, o.provider_id;


Why the error?


column "provider_id" doesn't exist



You tried to reference the output column name provider_id in the expression @(2 - provider_id) But expressions in the ORDER BY clause can only be formed from input columns. The manual:


  Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.


Bold emphasis mine. Postgres couldn't find provider_id in the list of input column names and responded with the error you quoted.