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

shirley

  • Apr 25th, 2017
 

Code
  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

  4.  

  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