“Column does not exist” with INNER JOIN and MAX query in ORDER BY
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.