Postgres SQL - Column does not exist

Question

SELECT nmemail as order_email, 
       dtorder, 
       vlOrder, 
       cohorts.cohortdate 
FROM   factorderline 
       JOIN (SELECT nmemail as cohort_email, Min(dtorder) AS cohortDate FROM  factorderline GROUP  BY cohort_email limit 5) cohorts 
ON order_email= cohort_email limit 5;

ERROR: column "order_email" does not exist

What is the problem with this query?

err, crap, sorry about the accidental vote to close. – David Wolever Mar 26 '16 at 17:11 — David Wolever, Mar 26 '16 at 17:11

score 1 · Accepted Answer · answered Mar 26 '16 at 17:15

The problem is most likely that the definition of the column alias hasn't been parsed at the time the join is evaluated; use the actual column name instead:

SELECT nmemail as order_email, 
       dtorder, 
       vlOrder, 
       cohorts.cohortdate 
FROM   factorderline 
JOIN (
  SELECT nmemail as cohort_email, Min(dtorder) AS cohortDate 
  FROM  factorderline 
  GROUP BY cohort_email limit 5
) cohorts ON nmemail = cohort_email 
limit 5;

Also, when using limit, you really should use an order by clause.

From the docs:

When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows.

score 1 · Answer 2 · answered Mar 26 '16 at 17:16

The problem is that output column names can't be used in joins.

From the documentation:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

Postgres SQL - Column does not exist

2 Answers2