Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

SQL Question for an ASP page

I'm creating an ASP form. The ASP page will make a SQL request and select a word from a choice of ten words.

Let's say I want the word "smart" to come up 10% of the time, the word "dumb" to come out 80% of the time and the word "average" to come out 10% of the time. How do I do this?

It's been a long time I haven't touched SQL but hey now something has come up.


Sunday, November 17, 2002


CREATE TABLE [tblWords] (
    [Word] [char] (50) NOT NULL ,
    [CumProb] [float] NULL ,
    )  ON [PRIMARY]


smart          0
dumb          0.1
average      0.9


WHERE CumProb =
  (SELECT MAX(CumProb) FROM tblWords
  WHERE CumProb <= RAND(DATEPART(ms, GETDATE()) * 100000 +
  DATEPART(ss, GETDATE()) * 1000 + DATEPART(mm, GETDATE())))

The ugliness in the query is due to the fact that T-SQL doesn't have a good nondeterministic random function.

There are no doubt other ways to solve this.

Mike Gunderloy
Sunday, November 17, 2002

I would actually do this a little differently.  This works on SQL Server 7/2000 as long as it's running on Windows 2000.

I'd create a table with multiple entries per word.  Something simple like

WordID int identity(1,1) primary key
Word varchar(50)

I'd put "smart" in it once, "dumb" 8 times and "average" once.  The following query will randomly select one of the rows:

Select Top 1 Word
From Words
Order by NEWID()

The NEWID() returns a unique indentifier.  Since Windows 2000 these have been randomly ordered.  You can find an article on this here: .  The number of rows determines the weighting.  Obviously if you want more granular weightings your table will need more rows.

Bill Graziano
Monday, November 18, 2002

*  Recent Topics

*  Fog Creek Home