This should do the trick, though it'll get slower as the table gets larger. Prohibitively slower after a couple thousand records, depending on hardware.
SELECT TOP 1 * FROM dbo.Quotes WHERE quo_wwwSite = 3 <!--- use CFQUERYPARAM though ---> ORDER BY rand() If you're going to have a lot of records in there, there are various other randomization schemes that will run better with large samples. The basic idea is to figure out how many items there are (say 1500), pick a random one (randRange(1, 1500)), and then pull just that row out directly. It's more complex than that, however. Here's a page that has a full implementation (it's PHP, but easy to follow), along with some discussion about advantages and disadvatages, and some more optimizations on the basic idea. http://www.greggdev.com/web/articles.php?id=6 cheers, barneyb On Thu, 3 Feb 2005 15:12:48 +1100, Duncan I Loxton <[EMAIL PROTECTED]> wrote: > Hi, > > I need to get one row and one row only from a sql table - cheesy > quotes for our corporate intranet..... > > The table is like this: > CREATE TABLE dbo.Quotes ( > quo_id int NOT NULL IDENTITY (1, 1), > quo_text text, > quo_author varchar(100), > quo_wwwSite int > ) > > Now wwwSite is the id of the website we are currently on, as this > table is shared between sites and quotes are different depending on > where you are. > > I need to get one random(ish) row from the quotes table from the > subset of wwwSite = 3. Is it possible to do in one query? or will I > have to run 2? > > I am using CF4.5 > > Thanks! > > -- > Duncan I Loxton > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192848 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
