stored procedures - SQL Server - How would I insert a RANK function to rows that are already sorted in ranked order? -

so, apparently, have right according professor except 1 column shows rank of columns shown in code below. i'm thinking that, essentially, has show row numbers off left side in own column. here instructions:

the sales manager create report ranks products both total sales , total sales quantity (each own column). create stored procedure returns following columns 2 new rank columns added.

product name | orders count | total sales value | total sales quantity

i know doesn't have column in assignment description, guess need it. here have far:

use onlinestore go  create proc spmanagerproductsalescount begin select      p.name 'product name',     isnull(count(distinct o.orderid), 0) 'orders count',     sum(isnull(o.ordertotal, 0)) 'total sales value',     sum (isnull(oi.orderitemquantity, 0)) 'total sales quantity'      product p inner join      orderitem oi on p.productid = oi.productid inner join      orders o on o.orderid = oi.orderid group     p.name order     'total sales value' desc, 'total sales quantity' desc end 

update: need in stored procedure , ctes can/should used. use ctes. pretty difficult me.

this select part of stored proc should show do:

declare @products table ( name varchar(50), id int ) declare @orderitems table ( id int, orderid int, productid int, orderitemquantity int ) declare @orders table ( orderid int, ordertotal decimal(18,2) )  insert @products values ('apple', 1) insert @products values ('orange', 2) insert @products values ('pear', 3) insert @products values ('melon', 4)  insert @orders values(1, 19.0) insert @orders values(2, 25.5) insert @orders values(3, 9.5) insert @orders values(4, 13.5) insert @orders values(5, 8.5)  insert @orderitems values(1, 1, 1, 20) insert @orderitems values(2, 1, 2, 10) insert @orderitems values(3, 2, 3, 5) insert @orderitems values(4, 2, 4, 4) insert @orderitems values(5, 3, 1, 10) insert @orderitems values(6, 3, 2, 5) insert @orderitems values(7, 4, 3, 3) insert @orderitems values(8, 4, 4, 2) insert @orderitems values(9, 5, 1, 5) insert @orderitems values(10, 5, 4, 2)  ;with summary  (     select p.name productname,      count(o.orderid) 'orders count',     isnull(sum(o.ordertotal),0) 'total sales value',     isnull(sum(oi.orderitemquantity),0) 'total sales quantity'     @products p     inner join @orderitems oi on oi.productid = p.id     inner join @orders o on o.orderid = oi.orderid      group p.name )  select productname, [orders count], [total sales value], [total sales quantity],  rank() on (order [total sales value] desc) valueranking, rank() on (order [total sales quantity] desc) quantityranking summary 

notice few things here. code can cut , pasted management studio query window , run such. starts table declarations , insert of sample data. when asking question useful if part of work; people more answer, if boring bit done!

count() not need isnull protection; returns 0, if there no values.

given final data, see valueranking , quantityrankings different (i fiddled data this, illustrate point). means final result can ordered 1 of them (or indeed other column - order not dependent on ranking).

hth