Random Number within limit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Random Number within limit

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2004-03-29 : 13:59:28

Hi,
Say I have a limit of 1 - 9999. I would like to assign a distinct number within this range to a record where fieldA = 'something'. How would I generate this number? So something like this.

Select random(number) from table1 where fieldA = 'something' AND number not in(select number from table1)

Question is, how do I get it so the numbers are between that range and how do I ensure they are somewhat random, so its not just returning the next available number. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-29 : 14:04:22

Create a tally table with numbers 1-9999. Then:

SELECT TOP 1 ID
FROM Numbers, Table1
WHERE fieldA = 'something' AND ID NOT IN (SELECT number FROM Table1)
ORDER BY NEWID()

Tally table:

CREATE TABLE [Numbers] (
[ID] [int] NOT NULL ,
CONSTRAINT [PK_Tally] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT 1-9999 values into table.

Tara

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-30 : 05:01:55

While I agree with the tally-table structure for a lot of these type of situations, in this particular instance.....would the following not be simpler?

select top 1 * from customer where code >= (select floor(rand()*10000)) order by code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources