ขอบคุณสำหรับการแนะนำใช้ TOP นะครับ
เขียนได้แล้ว ครับ
Code
declare @CategoryCode2 nvarchar(50),@CategoryCode nvarchar(50)
DECLARE authors_cursor CURSOR FOR
SELECT CategoryCode FROM tbSupplierProduct group by CategoryCode
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @CategoryCode
WHILE @@FETCH_STATUS = 0
BEGIN
insert into tbCategoryScore (categorycode,categoryname,ProductCount,ProductID)
Select top 1 S.CategoryCode,S.CategoryName,COUNT(S.CategoryCode) as ProductCount,Tb1.ProductID
From tbSupplierProduct S
left JOIN (Select top 1 CategoryCode,ProductID From tbSupplierProduct where CategoryCode = @CategoryCode )Tb1
On S.CategoryCode =Tb1.CategoryCode
where S.CategoryCode = @CategoryCode
Group By S.CategoryCode,CategoryName,Tb1.ProductID
Order By S.CategoryCode
FETCH NEXT FROM authors_cursor
INTO @CategoryCode
END
CLOSE authors_cursor
DEALLOCATE authors_cursor