Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

dense rank and Grouping

Code below does not give me totals correctly this works on AdventureWorks2008R2 Database

Data should look like Picture


Select VendorName,VendorRank,TotalDue,ProductName,ProductRank,ProductTotalDue  from (
Select *, DENSE_RANK() OVER (ORDER BY TotalDue DESC) VendorRank
		, DENSE_RANK() OVER (ORDER BY ProductTotalDue DESC) ProductRank from ( 
Select	v.Name as VendorName
		,p.Name as ProductName
		,SUM(ph.TotalDue) over(partition BY ph.VendorID) as TotalDue
		,SUM(ph.TotalDue) over(partition BY pd.ProductID) as ProductTotalDue
from Purchasing.PurchaseOrderHeader ph (nolock)
	inner Join Purchasing.PurchaseOrderDetail pd (nolock)
		on ph.PurchaseOrderID = pd.PurchaseOrderDetailID and ph.OrderDate between '2007-01-01' and '2008-06-30'
	inner join Production.Product p (nolock)
		on p.ProductID = pd.ProductID	
	Inner Join Purchasing.Vendor v (nolock)
		on v.BusinessEntityID = ph.VendorID	
Group By VendorID,pd.ProductID,v.Name,p.Name, ph.TotalDue
)  a
) b
Where VendorRank <= 5 and ProductRank <= 5
Order by TotalDue desc

Open in new window

EE-Rank3.png
Avatar of Leo Torres
Leo Torres
Flag of United States of America image

ASKER

Couldnt this be easier using CTE?
I think you want to add to the dense_rank also the partition by clause, to restart the product_rank per vendor ...
Avatar of Sean Stuber
Sean Stuber

try this...
I think you probably want to SUM the products by the purchase order details lines
rather than the purchase order header (which would be for all items)

SELECT   vendorname,
         vendorrank,
         totaldue,
         productname,
         productrank,
         producttotaldue
    FROM (SELECT a.*,
                 DENSE_RANK() OVER (ORDER BY totaldue DESC) vendorrank,
                 DENSE_RANK() OVER (PARTITION BY vendorname ORDER BY producttotaldue DESC)
                     productrank
            FROM (SELECT DISTINCT
                         v.name AS vendorname,
                         p.name AS productname,
                         SUM(ph.totaldue) OVER (PARTITION BY ph.vendorid) AS totaldue,
                         SUM(pd.linetotal) OVER (PARTITION BY ph.vendorid, pd.productid)
                             AS producttotaldue
                    FROM purchasing.purchaseorderheader ph                                (nolock)
                        INNER JOIN purchasing.purchaseorderdetail pd                      (nolock)
                            ON     ph.purchaseorderid = pd.purchaseorderdetailid
                               AND ph.orderdate BETWEEN '2007-01-01' AND '2008-06-30'
                        INNER JOIN production.product p                                   (nolock)
                            ON p.productid = pd.productid
                        INNER JOIN purchasing.vendor v                                    (nolock)
                            ON v.vendorid = ph.vendorid) a) b
   WHERE vendorrank <= 5 AND productrank <= 5
ORDER BY totaldue DESC, producttotaldue DESC
also note,  DENSE_RANK will allow for ties, so, if two vendors or two products have the same totals you'll get multiple rows for a given rank.

Thus, it's possible this query could return more than 25 rows.

If you want at most 5 of each rank, then use ROW_NUMBER instead of DENSE_RANK
and extend the ORDER BY portion of the OVER clause to define how you want to handle ties.
Leo, no points here as you are in good hands above. Just posting a reference that might be useful: Analytical SQL : Where do you rank? - http:A_1555.html
It should explain what Sean is saying about DENSE_RANK(), which is more like TOP WITH TIES.
Thanks Kevin,

sdstuber, Once I have the top 5 verdors I need the top five products for those vendors This may be more cleanly done with CTE. These inner queries are killing me :)

repeats don't matter you have to have repeats on vender rank because those are the top 5 products for those top 5 vendor so it makes sense in this case



This gets me correct top 5 Vendors now I need to add the next CTE and tie them together some how
With Vendor as (
Select VendorID,VendorName,VendorRank,TotalDue  from (
Select *, DENSE_RANK() OVER (ORDER BY TotalDue DESC) VendorRank 
	From (
			Select	v.BusinessEntityID as VendorID
			,v.Name as VendorName
			,SUM(ph.TotalDue) over(partition BY ph.VendorID) as TotalDue
			from Purchasing.PurchaseOrderHeader ph (nolock)
				inner Join Purchasing.PurchaseOrderDetail pd (nolock)
					on ph.PurchaseOrderID = pd.PurchaseOrderDetailID and ph.OrderDate between '2007-01-01' and '2008-06-30'	
				Inner Join Purchasing.Vendor v (nolock)
					on v.BusinessEntityID = ph.VendorID	
		
)  a
) b
Where VendorRank <= 5

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Something is still wrong data should like like the image I sent
yep, they both produce results exactly like that.

the only difference is my Adventureworks database doesn't have data for 2007, I had to go back to 2004.

Structurally they are the same though.

If you're looking to reproduce an exact screen shot,  is that because you have already been given the answer?  Am I doing your homework for you?
Kinda sorta, work slow now... just honing my query skills you just became my tutor..

Thanks for the help

I will have to do checks on my data then to make sure query is reflecting correct results..

Thanks for the Assists only using resources to bet of my ability

Thanks for the help
I found the issue Sum partition had to be done one line Items not total it was aggregating twice
and the this join was changed
ON     ph.purchaseorderid = pd.purchaseorderid

sorry for the delay afternoon work picked up

WITH a
     AS (SELECT DISTINCT
                v.name AS vendorname,
                p.name AS productname,
                SUM(pd.linetotal) OVER (PARTITION BY ph.vendorid) AS totaldue,
                SUM(pd.linetotal) OVER (PARTITION BY ph.vendorid, pd.productid) AS producttotaldue
           FROM purchasing.purchaseorderheader ph (nolock)
               INNER JOIN purchasing.purchaseorderdetail pd (nolock)
                   ON     ph.purchaseorderid = pd.purchaseorderid
                      AND ph.orderdate BETWEEN '2007-01-01' AND '2008-06-30'
               INNER JOIN production.product p (nolock)
                   ON p.productid = pd.productid
               INNER JOIN purchasing.vendor v (nolock)
                   ON v.BusinessEntityID = ph.vendorid),
     b
     AS (SELECT a.*,
                DENSE_RANK() OVER (ORDER BY totaldue DESC) vendorrank,
                DENSE_RANK() OVER (PARTITION BY vendorname ORDER BY producttotaldue DESC)
                    productrank
           FROM a)
SELECT   vendorname,
         vendorrank,
         totaldue,
         productname,
         productrank,
         producttotaldue
    FROM b
   WHERE vendorrank <= 5 AND productrank < 5
ORDER BY totaldue DESC, producttotaldue DESC

Open in new window

Thank you! for your prompt responses