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
Sample table: production.Product
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join