w3resource

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.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 --

Click to view Full table

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 dense rank of Quantity within each LocationID partition
    DENSE_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 DENSE_RANK() function is used to calculate the dense rank of Quantity within each LocationID partition. Dense ranking assigns consecutive rank numbers to rows with the same Quantity value, without any gaps.
  • 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|   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.



Follow us on Facebook and Twitter for latest update.