SQL Over Partition By Over Partition By

Question

I have the following code and it works fine when I am referencing one 'leadno' as it returns the highest sum of individual products

select t.*
from (Select t2.leadno, t1.quoteno, t1.cn_ref, sum(t1.qty/100) as 't_qty',
             ROW_NUMBER() Over (Partition By t1.cn_ref order by sum(qty/100) desc) as RN 
      From dba.quotelne t1 INNER JOIN
           dba.quotehdr t2
           ON t1.quoteno = t2.quoteno
      Where leadno = 31665 and t1.statusflag = 'A' and t2.statusflag = 'A' 
      Group By t2.leadno, t1.quoteno, t1.cn_ref
     ) t
where rn = 1

But when I add more that one leadno it returns doesn't return all the quoteno it only returns the values of the highest sum(qty/100), so Is there a way to run a ROW_NUMBER() Over (Partition By in a ROW_NUMBER() Over (Partition By)

I hope this makes sense, but if not I will try to explain, a little further, each 'lead' may have one or more 'quotes' against it. Each 'quote' will have a number of products listed. These may be duplicated (hence why I am using sum(qty/100) What I need to happen is all leadno to be displayed, the unique cn_ref and the highest quantity RN = 1 against the leadno

Answer 1

I am not 100% sure but I think you need to put the leadno in the Partition to accomplish what you need:

select t.*
    from (Select t2.leadno, t1.quoteno, t1.cn_ref, sum(t1.qty/100) as 't_qty',
         ROW_NUMBER() Over (Partition By t2.leadno, t1.cn_ref order by sum(qty/100) desc) as RN 
      From dba.quotelne t1 INNER JOIN
           dba.quotehdr t2
       ON t1.quoteno = t2.quoteno
      Where leadno = 31665 and t1.statusflag = 'A' and t2.statusflag = 'A' 
      Group By t2.leadno, t1.quoteno, t1.cn_ref
     ) t
where rn = 1

Beside that, you shouldn't devide the qty in the order by, it doens't change the order because you devide every value by 100, but the engine has to devide every record before it can create the summary

Answer 2

Why your need to use ROW_NUMBER() OVER (PARTITION BY)?

If I understand your needs, you want the the follow values: leadno, cn_ref, max(sum(qty/100))

So, try this query:

Select t2.leadno, t1.cn_ref, max(sum(t1.qty/100)) as 't_qty',
  From dba.quotelne t1 INNER JOIN
       dba.quotehdr t2
       ON t1.quoteno = t2.quoteno
  Where leadno = 31665 and t1.statusflag = 'A' and t2.statusflag = 'A' 
  Group By t2.leadno