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


Click to view Full table

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

How to select rows with no matching entry in another table?

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

Ref: https://bit.ly/2QPhaD3

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook