I'm working on a project where I want to return related content. The sproc I wrote seems to work ok but because I was sorting by the insert date or the title, it would always return the same thing for all content closely associated.
So how do I get it to return only the closely related content AND randomize it?
Here is the what I did:
FROM #tmpRelated r
JOIN Content c ON r.ContentID = c.ContentID
ORDER BY r.TotalRelevance DESC, r.TotalMatches DESC, newid()
Ordering by newid() will return a random row in Sql Server. In testing this, it worked perfectly. Cool!
Here is the article I found on this. It gives ways to return random rows in Sql Server, MySql, PostegreSql, Oracle, and IBM D2.