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.

thanks

Ron
Sunday, November 17, 2002

Table:

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

Data:

smart          0
dumb          0.1
average      0.9

Query:

SELECT Word FROM tblWORDS
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

Words
-------------
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: http://www.sqlteam.com/item.asp?ItemID=8747 .  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