0

I have a query. I use an alias in order by when using row_number and I got

[42703] ERROR: column "total_comments" does not exist error

How can I fix this?

 select
      cr_seller_history_id,
      c.created_at,
      company_name,
      business_name,
      brand,
      kep_mail,
      address,
      phone,
      mail,
      slug,
      name,
      point,
      contact_positive,
      contact_negative,
      product_number,
      (product_positive + product_negative) as total_comments,
      ROW_NUMBER() OVER(ORDER BY total_comments) as rank
    from cr_companies a
      INNER JOIN cr_sellers b ON a.cr_company_id = b.cr_company_id
      INNER JOIN cr_seller_histories c ON b.cr_seller_id = c.cr_seller_id
      WHERE DATE(c.created_at) = DATE 'yesterday'
    ORDER BY total_comments DESC NULLS LAST
1

The error is (from the column used inside the OVER) in SELECT. You can't use a column alias defined in the select list for another column expression in the same select list:

SELECT
      -- 
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY total_comments) AS rank    
                               -- ^^ this is throwing the error

You just have to duplicate the expression inside the OVER (ORDER BY ...):

SELECT
      -- 
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY product_positive + product_negative) AS rank
---
ORDER BY 
      total_comments DESC NULLS LAST ;       -- you can keep it here

Irrelevant to the issue but this condition will stop the optimizer from using any indexes on (created_at):

WHERE DATE(c.created_at) = DATE 'yesterday'

I'd write it like this, so indexes can be used more effectively:

WHERE c.created_at >= DATE 'yesterday'
  AND c.created_at  < DATE 'today'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.