AdventureWorks Database: Rank the products by the specified inventory locations

Last update on December 20 2024 11:56:45 (UTC/GMT +8 hours)

116. From the following table write a query in SQL to rank the products in inventory, by the inventory locationID values between 3 and 4, according to their quantities. Divide the result set by LocationID and sort the result set on Quantity in descending order.

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.