Tuesday, February 3, 2009

Getting random rows from Sql Server

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.