Subquery top X of random rows not working

[thalassa3000]

Hi,

I'm doing a subquery to select the top 5 of products for each supplier. The selection needs to be done randomly on the products for each supplier.

For this I have made the following query (based on Allen Browne's example):

SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY RND(Dupe.GTIN) DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;

The query returns a random number of products, but not a top 5. So for supplier X one time 3 products and the next time for supplier X 7 products.

The query without the RND function, so just the top 5 works fine:

SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY Dupe.GTIN DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;

I'am real stuck. Can anyone please help me out!

Thx!

 

[jdraw]

Please explain how Top 5 and Random fit together.
I can see the Top 5 Sales for Products by supplier.
I can see the Top 5 (lowest times) for Runners in a Race, but need an example of Top X and Random.

 

[thalassa3000]

It is the random top 5 of products in the assortment of a supplier.

So say if a supplier has 19 products in it's assortment, I want to select 5 random products for the supplier. The needs to be done for each supplier (supplierID) and it's products (productid), which are store in the table tblProducts_temp, with the columns supplierID and ProductID.