Can anyone help me to understand how to use RAND() function in SQL Server?
I have a scenario in which I want to fetch random products from products table in a random order on the basis of rating.
Also, in this I want to give preference to higher rating products over lower rating products to come at top order.
But it would not be like everytime only higher rating products will come at top in the order; sometimes lower rating products should be on top.
As of now, I did it through NEWID(), which gives me random order every time, but it doesn't consider ranking of products.
Table columns: ProductID, ProductName, Rating
SELECT ROW_NUMBER() OVER(ORDER BY NEWID() ASC) AS ProductOrder, ProductID, Rating
FROM dbo.VIHC_Products
ORDER BY ProductOrder
This query gives me random products everytime but does not fullfil my criteria of getting higher rating products at top over lower rating products most times.
Just multiply the row number with the rating and order descending:
SELECT Rating * ROW_NUMBER() OVER(ORDER BY NEWID() ASC) AS ProductOrder, ProductID, Rating
FROM dbo.VIHC_Products
ORDER BY ProductOrder DESC