AdventureWorks Database: Rank the products by the specified inventory locations
SQL Query - AdventureWorks: Exercise-116 with Solution
116. From the following table write a query in SQL to rank the products in inventory, by the specified inventory locations, according to their quantities. Divide the result set by LocationID and sort the result set on Quantity in descending order.
Sample table: Production.ProductInventoryproductid|locationid|shelf|bin|quantity|rowguid |modifieddate | ---------+----------+-----+---+--------+------------------------------------+-----------------------+ 1| 1|A | 1| 408|47a24246-6c43-48eb-968f-025738a8a410|2014-08-08 00:00:00.000| 1| 6|B | 5| 324|d4544d7d-caf5-46b3-ab22-5718dcc26b5e|2014-08-08 00:00:00.000| 1| 50|A | 5| 353|bff7dc60-96a8-43ca-81a7-d6d2ed3000a8|2014-08-08 00:00:00.000| 2| 1|A | 2| 427|f407c07a-ca14-4684-a02c-608bd00c2233|2014-08-08 00:00:00.000| 2| 6|B | 1| 318|ca1ff2f4-48fb-4960-8d92-3940b633e4c1|2014-08-08 00:00:00.000| 2| 50|A | 6| 364|d38cfbee-6347-47b1-b033-0e278cca03e2|2014-08-08 00:00:00.000| 3| 1|A | 7| 585|e18a519b-fb5e-4051-874c-58cd58436c95|2008-03-31 00:00:00.000| 3| 6|B | 9| 443|3c860c96-15ff-4df4-91d7-b237ff64480f|2008-03-31 00:00:00.000| 3| 50|A | 10| 324|1339e5e3-1f8e-4b82-a447-a8666a264f0c|2008-03-31 00:00:00.000| 4| 1|A | 6| 512|6beaf0a0-971a-4ce1-96fe-692807d5dc00|2014-08-08 00:00:00.000| 4| 6|B | 10| 422|2c82427a-63f1-4877-a1f6-a27b4d201eb6|2014-08-08 00:00:00.000| 4| 50|A | 11| 388|fd912e69-efa2-4ab7-82a4-03f5101af11c|2014-08-08 00:00:00.000| 316| 5|A | 11| 532|1ee3dbd3-2a7e-47dc-af99-1b585575efb9|2008-03-31 00:00:00.000| -- more --
Sample Solution:
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,DENSE_RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
Sample Output:
productid|name |locationid|quantity|rank| ---------+--------------+----------+--------+----+ 495|Paint - Blue | 3| 49| 1| 494|Paint - Silver| 3| 49| 1| 493|Paint - Red | 3| 41| 2| 496|Paint - Yellow| 3| 30| 3| 492|Paint - Black | 3| 17| 4| 495|Paint - Blue | 4| 35| 1| 496|Paint - Yellow| 4| 25| 2| 493|Paint - Red | 4| 24| 3| 492|Paint - Black | 4| 14| 4| 494|Paint - Silver| 4| 12| 5|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Get the differences between the maximum and minimum orderdate.
Next: Return the top ten employees ranked by their salary.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics