Cameron Warren Cameron Warren - 2 months ago 24
SQL Question

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;

Answer

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;