sqlsql-serverrankadventureworks

Invalid column name with RANK OVER function SQL


Attempting to create a table with the total quantity sold by product and select the third highest quantity sold product segmented by date. Keep getting error

Invalid Column name

for the alias for my RANK () OVER statement:

select 
    RANK () OVER (PARTITION BY t3.orderdate order by t3.total_amt_ordered) as ranking,
    t3.productid, 
    t3.orderdate,
    t3.total_amt_ordered
from 
    (select 
         t2.productid,
         t1.orderdate,
         SUM(t2.orderqty) as total_amt_ordered 
     from
         saleslt.salesorderheader t1 
     inner join 
         saleslt.salesorderdetail t2 on t1.salesorderid = t2.salesorderid 
     group by 
         productid, orderdate) t3
where 
    ranking = 3; 

Solution

  • The below query will return all the product ids partitioned at order date level and all the order quantities which ranked at three.

    SELECT * FROM
         (
            select 
                    DENSE_RANK () OVER (PARTITION BY t3.orderdate order by  
                    t3.total_amt_ordered DESC ) as ranking,
                    t3.productid, 
                    t3.orderdate,
                    t3.total_amt_ordered
              from ( 
                     select 
                             t2.productid,
                             t1.orderdate,
                             SUM(t2.orderqty) as total_amt_ordered 
                       from
                             saleslt.salesorderheader t1 
                         inner join
                             saleslt.salesorderdetail t2 
                         on t1.salesorderid=t2.salesorderid 
                       group by productid, orderdate) t3
          ) Z
    where Z.ranking= 3;