AdventureWorks Database: Rank the products in inventory according to their quantities
119. From the following tables write a query in SQL to rank the products in inventory with locationID values between 3 and 4 according to their quantities. The result set is partitioned by LocationID and logically ordered by Quantity. Return productid, name, locationid, quantity, and rank.
Sample table: production.productinventory
productid|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 table: production.Product
productid|name |productnumber|makeflag|finishedgoodsflag|color |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate |sellenddate |discontinueddate|rowguid |modifieddate |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
1|Adjustable Race |AR-5381 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
2|Bearing Ball |BA-8327 |false |false | | 1000| 750| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
3|BB Ball Bearing |BE-2349 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
4|Headset Ball Bearings |BE-2908 |false |false | | 800| 600| 0| 0| | | | | 0| | | | | |2008-04-30 00:00:00.000| | |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
316|Blade |BL-2036 |true |false | | 800| 600| 0| 0| | | | | 1| | | | | |2008-04-30 00:00:00.000| | |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
317|LL Crankarm |CA-5965 |false |false |Black | 500| 375| 0| 0| | | | | 0| |L | | | |2008-04-30 00:00:00.000| | |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
318|ML Crankarm |CA-6738 |false |false |Black | 500| 375| 0| 0| | | | | 0| |M | | | |2008-04-30 00:00:00.000| | |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
-- more --
Sample Solution:
-- Selecting specific columns from the ProductInventory table and the Product table
SELECT
-- Selecting the ProductID column from the ProductInventory table
i.ProductID,
-- Selecting the Name column from the Product table
p.Name,
-- Selecting the LocationID column from the ProductInventory table
i.LocationID,
-- Selecting the Quantity column from the ProductInventory table
i.Quantity,
-- Calculating the rank of Quantity within each LocationID partition
RANK() OVER (
-- Partitioning the data by LocationID and ordering by Quantity in descending order
PARTITION BY i.LocationID
ORDER BY i.Quantity DESC
) AS Rank
-- Joining the ProductInventory table with the Product table on ProductID
FROM
Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
-- Filtering records for LocationID values between 3 and 4
WHERE
i.LocationID BETWEEN 3 AND 4
-- Ordering the result set by LocationID
ORDER BY
i.LocationID;
Explanation:
- This SQL code selects specific columns from the ProductInventory table and the Product table.
- It retrieves data related to product inventory, including ProductID, Product Name, LocationID, Quantity, and the rank of Quantity within each LocationID partition.
- The RANK() function is used to calculate the rank of Quantity within each LocationID partition. Ranking assigns consecutive rank numbers to rows with the same Quantity value, with any gaps in the ranking sequence being skipped.
- The data is partitioned by LocationID and ordered by Quantity in descending order within each partition.
- Records are filtered to include only those with LocationID values between 3 and 4.
- The result set is ordered by 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| 3|
496|Paint - Yellow| 3| 30| 4|
492|Paint - Black | 3| 17| 5|
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: Divide rows into defined groups based on SalesYTD.
Next: Find the salary of top ten employees.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.