Randomly SELECT a Result using SQL Quiery

[Nathan]

I am planning to add a small database of FAQs, and the system will randomly show one on each site page.

I am quite capable at creating SQL quiries, but I am looking for the following functionally:

Rather than for example SELECT (the) TOP 10 of the results, I want to randomly choose one of the results. Is this possible within the quiery or do I need to just SELECT all the results and then randomly select one? (Seems like an unessary server overhead)

Any advice would be very welcome.

Thanks
Nathan

 

[Nathan]

I've just found this:

http://aspnet.4guysfromrolla.com/articles/051003-1.aspx

SELECT TOP N ProductID, Name, Description, Price
FROM Products
ORDER BY NEWID()


They say it only works on SQL Server 7.0. What version Server does Jodohost have?

Nathan

 

[Stephen]

2000, or 8.0

If it says it works with 7 I bet that means 7+

 

[shamrox]

Nathan,

Another couple of options could be randomize it. You can select * from faq table then take a random number between 1 and length of the dataset and take that row.

You can build a simple serializable object that holds the faq and build a list, store it in session then everytime the page loads during that session you can grab it from the session. You will have the list in session and you can randomly grab from that list. This will be less traffic on your database. It will be one call to the database when the user first comes to the site, rather then a hit everytime the page loads.

Just a couple of options.

 

[JiltedCitizen]

How about these articles?

http://www.sqlteam.com/searchresults.asp?SearchTerms=random+select