Select Distinct cID  from #temptable

Select Distinct cID,Name from #temptable

Set @groupID = 15

--Declare @groupID int = 14
Declare @group int

 

Select @group = isnull(ParentID,0) from V020U18NUC_AGC.Groups
where ParentID = @groupID

If (@group = 0) -- there is no SubCats
 Begin
   Select * from V020U18NUC_AGC.Products
   where cID = @groupID
 End
Else -- there is subcats
 Begin
   --create TempTable

CREATE TABLE #TempTable
(
pID int IDENTITY PRIMARY KEY,
cID int,
Name varchar(50),
short varchar (300),
long varchar(4000),
man varchar(50),
Made datetime,
price money,
Weight int,
qty int,
Thumb varchar(30),
Pic varchar(30)
)
SET IDENTITY_INSERT #temptable ON

INSERT into #temptable
(
  pId, 
  cID, 
  name,
Short,Long,Man,Made,Price,Weight,qty,thumb,pic
)
SELECT Products.pID, Products.cID, Products.Name, Products.Short, Products.Long, Products.Man, Products.Made, Products.Price, Products.Weight, Products.Qty, Products.Thumb, Products.Pic
FROM V020U18NUC_AGC.Groups INNER JOIN V020U18NUC_AGC.Products ON Groups.GroupID = Products.cID
WHERE (((Groups.parentID)=@groupID))order by newid()

--get all subcats
-- select random product from each subcat
--fill temptable


 end

Select  cID, Max(Name) from #temptable GROUP BY CID
Drop Table #TempTable