SQL which LEFT JOIN with top 1

I'm looking for SQL query which do the LEFT JOIN with top 1

Select p.*, w2.Wid
from product p
 LEFT JOIN
                  (
                        Select  top 1 Location, Qty, Wid
                        From Warehouse w
                        where w.ProductID = p.ProductID
                        Order by Qty desc
                  ) w2 ON w.ProductID = p.ProductID

Product
-------------------
ProductID             Name
ABC                        zzz
EFG                        yyy
HID                        xxx


Warehouse
---------
Wid  ProductID      Qty
1    ABC        25
2    ABC        35
3    EFG        25
4    EFG        NULL
5    EFG        26
6    HID            NULL
7    HID            NULL



Result should be
-----------------
ProductID             Name      Wid
ABC                        zzz            2
EFG                        yyy            5
HID                        xxx              6
Bharat GuruAsked:
Who is Participating?
 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
You can get a ROW_NUMBER() off the table as ordered by Qty and then join based off that criteria.

SELECT p.ProductId,p.Name,w2.Wid FROM
Product p
INNER JOIN
	(SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Qty Desc) AS "RN",ProductID,Wid,Qty
	FROM Warehouse
	) w2 ON w2.ProductId = p.ProductID AND w2.RN = 1

Open in new window


Full example:
CREATE TABLE #Product (ProductID nvarchar(50),Name nvarchar(50))
CREATE TABLE #Warehouse (Wid int, ProductId nvarchar(50), Qty int)

INSERT INTO #Product (ProductID,Name)
VALUES ('ABC','zzz'),('EFG','yyy'),('HID','xxx')

INSERT INTO #Warehouse (Wid, ProductId, Qty)
VALUES (1,'ABC',25),(2,'ABC',35),(3,'EFG',25),(4,'EFG',NULL),(5,'EFG',26),(6,'HID',NULL),(7,'HID',NULL)

SELECT p.ProductId,p.Name,w2.Wid FROM
#Product p
INNER JOIN
	(SELECT ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Qty Desc) AS "RN",ProductID,Wid,Qty
	FROM #Warehouse
	) w2 ON w2.ProductId = p.ProductID AND w2.RN = 1

DROP TABLE #Product
DROP TABLE #Warehouse

Open in new window


Results:
ABC	zzz	2
EFG	yyy	5
HID	xxx	6

Open in new window

0
 
Bharat GuruAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.