w3resource

AdventureWorks Database: Rank the products in inventory according to their quantities

SQL Query - AdventureWorks: Exercise-119 with Solution

119. From the following tables write a query in SQL to rank the products in inventory the specified inventory locations 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


Click to view Full table

Sample table: production.Product


Click to view Full table

Sample Solution:

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  
    ,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|   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.



Follow us on Facebook and Twitter for latest update.