Leo Torres
asked on
dense rank and Grouping
Code below does not give me totals correctly this works on AdventureWorks2008R2 Database
Data should look like Picture
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
EE-Rank3.png
I think you want to add to the dense_rank also the partition by clause, to restart the product_rank per vendor ...
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.purchaseorderhe ader ph (nolock)
INNER JOIN purchasing.purchaseorderde tail 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
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.purchaseorderhe
INNER JOIN purchasing.purchaseorderde
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.
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.
It should explain what Sean is saying about DENSE_RANK(), which is more like TOP WITH TIES.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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
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
ASKER
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
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
ASKER
Thank you! for your prompt responses
ASKER