Why can't i limit my rank()?

Question

I am trying to do a limit on the rank function but I can't seem to make it work. What am I missing? So far I used a WHERE/HAVING function on the outer query: WHERE rank < 10 but I get the error that there is no Rank Column.

I've been looking around and many people used an alias also.

    SELECT a.customer_name ,
       a.unit ,
       sum(a.price) - sum(b.price) AS "highest revenue" ,
       rank() over (partition BY a.unit
                    ORDER BY sum(a.price) - sum(b.price) DESC) AS "Rank"
FROM
  (SELECT customer_name ,
          unit ,
          price
   FROM table1
   WHERE booked BETWEEN '12/01/15' AND '12/31/15') a
JOIN
  (SELECT customer_name ,
          unit ,
          price
   FROM table1
   WHERE booked BETWEEN '11/01/15' AND '11/30/15') b ON a.unit = b.unit
GROUP BY a.customer_name,
         a.unit
ORDER BY a.unit,
         sum(a.price) - sum(b.price) DESC, rank() over (partition BY a.unit ORDER BY sum(a.price) - sum(b.price) DESC) DESC

Answer 1

You cannot use the alias in the query itself, you should wrap it all in an outer query:

SELECT customer_name, unit, "highest revenue", rank
FROM (
  SELECT a.customer_name, a.unit,
         sum(a.price - b.price) AS "highest revenue",
         rank() OVER (PARTITION BY a.unit
                      ORDER BY sum(a.price - b.price) DESC) AS rank
  FROM
    (SELECT customer_name, unit, price
     FROM table1
     WHERE booked BETWEEN '12/01/15' AND '12/31/15') a
  JOIN
    (SELECT customer_name, unit, price
     FROM table1
     WHERE booked BETWEEN '11/01/15' AND '11/30/15') b USING (unit)
  GROUP BY a.customer_name, a.unit) sub
ORDER BY unit, "highest revenue" DESC, rank DESC;