Correlated Sub Query

Compose an SQL statement to show a list of two least expensive vendors (suppliers) for each raw material. In the result table, show Material ID, Material Description, Vendor ID, Vendor Name, and Supplier’s unit price. Sort the result table by Material_ID and unit price in ascending order. Note: if a raw material has only one supplier(vendor), that vendor should also be in the result (output) table [hint: use a correlated sub query].

Showing Answers 1 - 1 of 1 Answers


  • Apr 25th, 2017

  1. SELECT Rank() OVER (PARTITION BY MaterialID ORDER BY UnitPrice ASC) AS [Ranking], MaterialID, Description, VendorID, VendorName, UnitPrice

  2. INTO tempdb..ProductByVendor

  3. FROM tt


  5. SELECT MaterialID, Description, VendorID, VendorName, UnitPrice

  6. FROM tempdb..ProductByVendor

  7. WHERE Ranking<3

  8. ORDER BY MaterialID, UnitPrice ASC

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions