question

BOBEE avatar image
BOBEE asked

How to find the top of all categories without using rank and view function

IDENTIFY THE THREE IMPORTANT CITIES .SHOW THE BREAK DOWN LEVEL OF TOP LEVEL -- PRODUCT CATEGORY AGAINST CITY -using adventure works - This is my query..but how to do without using rank and with and view function WITH G1 AS ( SELECT F.City,D.Name,SUM(A.UnitPrice * A.OrderQty) VALUE,RANK()OVER(PARTITION BY F.CITY ORDER BY SUM(A.UnitPrice * A.OrderQty) DESC)AS RANKK,SUM(OrderQty)AS QTY FROM Sales.SalesOrderDetail AS A JOIN Production.Product AS B ON(A.ProductID=B.ProductID) JOIN Production.ProductSubcategory AS D ON (D.ProductSubcategoryID=B.ProductSubcategoryID) JOIN Production.ProductCategory AS E ON (D.ProductCategoryID=E.ProductCategoryID) JOIN Sales.SalesOrderHeader AS C ON (A.SalesOrderID=C.SalesOrderID) JOIN Person.Address AS F ON (F.AddressID=C.ShipToAddressID) GROUP BY D.Name,F.City ) SELECT TOP(3) CITY,NAME,VALUE FROM G1 WHERE RANKK = 1 ORDER BY VALUE DESC;
subqueryrankwith-clause
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
BOBEE avatar image
BOBEE answered
--FINALLY I GOT THE ANSWER BY MYSELF-- SELECT TOP(3) FIN.City,DER.Name,FIN.MAXVALUE FROM (SELECT G.City,MAX(G.VALUE)AS MAXVALUE FROM (SELECT f.City,D.Name,sum(LineTotal) VALUE FROM Sales.SalesOrderDetail AS A JOIN Production.Product AS B ON(A.ProductID=B.ProductID) JOIN Production.ProductSubcategory AS D ON (D.ProductSubcategoryID=B.ProductSubcategoryID) JOIN Production.ProductCategory AS E ON (D.ProductCategoryID=E.ProductCategoryID) JOIN Sales.SalesOrderHeader AS C ON (A.SalesOrderID=C.SalesOrderID) JOIN Person.Address AS F ON (F.AddressID=C.ShipToAddressID) GROUP BY F.City ,D.Name)AS G GROUP BY G.City ) AS FIN INNER JOIN (SELECT f.City,D.Name,sum(LineTotal) VALUE FROM Sales.SalesOrderDetail AS A JOIN Production.Product AS B ON(A.ProductID=B.ProductID) JOIN Production.ProductSubcategory AS D ON (D.ProductSubcategoryID=B.ProductSubcategoryID) JOIN Production.ProductCategory AS E ON (D.ProductCategoryID=E.ProductCategoryID) JOIN Sales.SalesOrderHeader AS C ON (A.SalesOrderID=C.SalesOrderID) JOIN Person.Address AS F ON (F.AddressID=C.ShipToAddressID) GROUP BY F.City ,D.Name) AS DER ON (FIN.City=DER.City AND FIN.MAXVALUE=DER.VALUE ) ORDER BY VALUE DESC
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

this is a good one...to simply you can use view for your queries like :- create view G1 AS SELECT F.City,D.Name,SUM(A.UnitPrice * A.OrderQty) VALUE,SUM(OrderQty)AS QTY FROM Sales.SalesOrderDetail AS A JOIN Production.Product AS B ON(A.ProductID=B.ProductID) JOIN Production.ProductSubcategory AS D ON (D.ProductSubcategoryID=B.ProductSubcategoryID) JOIN Production.ProductCategory AS E ON (D.ProductCategoryID=E.ProductCategoryID) JOIN Sales.SalesOrderHeader AS C ON (A.SalesOrderID=C.SalesOrderID) JOIN Person.Address AS F ON (F.AddressID=C.ShipToAddressID) GROUP BY D.Name,F.City select top 3 b.city,b.name,b.value from (select city,max(value) value from g1 group by city ) a join (select city,name,max(value) value from g1 group by city,name ) b on a.city=b.city and a.value=b.value order by b.value desc
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.