Invalid column name with RANK OVER function SQL

Question

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;

score 3 · Answer 1 · edited May 24 '23 at 04:38

3

WHERE is evaluated before RANK, so you can't use it directly without Derived Table/CTE, but it's also calculated after SUM, resulting in this query:

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

edited May 24 '23 at 04:38

Chris Halcrow

28,994
18
176
206

answered Sep 21 '16 at 20:52

dnoeth

59,503
4
39
56

This gives me nulls. – Cameron Warren Sep 21 '16 at 21:05
@CameronWarren: NULLs? You probably need to switch to `DENSE_RANK` (or maybe to `ROW_NUMBER`) – dnoeth Sep 21 '16 at 21:21

Teja · Accepted Answer · 2016-09-21T21:24:53.487

2

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;

edited Sep 21 '16 at 21:24

answered Sep 21 '16 at 20:38

Teja

13,214
36
93
155

Thanks! Getting error now on the where statement. Can I not include it? – Cameron Warren Sep 21 '16 at 20:47
What error are you getting? And also does my query work without where clause? If so what are the final columns in the output you are seeing...? – Teja Sep 21 '16 at 20:49
It doesn't work without where clause. Showing :Msg 102, Level 15, State 1, Line 48 Incorrect syntax near ';'. – Cameron Warren Sep 21 '16 at 20:51
Can you try running the inner most queries and see how the results are coming out... – Teja Sep 21 '16 at 20:52
1

You need to add an alias for the Derived Table, i.e. before the final WHERE – dnoeth Sep 21 '16 at 20:53
So this code works except it's only giving me results for one of the dates (table contains two order dates 6-04 and 6-05 - only giving me results for 6-04 but it is correct for 6-04 – Cameron Warren Sep 21 '16 at 21:04
118 values for 6-04 and 135 for 6-05 – Cameron Warren Sep 21 '16 at 21:11
Are there any duplicates in your ordered value at date level partition... ?? If its the case then u shud use dense rank instead of rank... – Teja Sep 21 '16 at 21:12
DENSE_RANK did the trick. Only weird thing is I get the 4th and 5th highest value for 6-05 (they are both the same amount) - but 6-04 is correct. Any reason I'd get the 4th value down instead of the third (the second and third value are also tied - so maybe it's in that.) – Cameron Warren Sep 21 '16 at 21:17
Yes if there are n values which are in tie then the following value will be assigned a rank which is n less than the rank assigned to the tied value. This concern can be solved using dense rank as all ranks are consecutive. – Teja Sep 21 '16 at 21:20

score 1 · Answer 3 · answered Sep 21 '16 at 20:41

You can use this way, You cannot use ranking query and same in the where condition

;WITH cte
AS (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)
SELECT
    *
FROM cte
WHERE ranking = 3;

This gives all nulls. – Cameron Warren Sep 21 '16 at 21:05 — Cameron Warren, Sep 21 '16 at 21:05

Invalid column name with RANK OVER function SQL

3 Answers3