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].

Interview Candidate
Mar 15th, 2017
3
6444

SQL

Answer

Showing Answers 1 - 3 of 3 Answers

shirley

Apr 25th, 2017

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


INTO tempdb..ProductByVendor


FROM tt


 


SELECT MaterialID, Description, VendorID, VendorName, UnitPrice


FROM tempdb..ProductByVendor


WHERE Ranking<3


ORDER BY MaterialID, UnitPrice ASC