How to get the row number when using alias in orderby

Question

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

Answer 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'