get 3 random numbers that don't match

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 2012 Forums

Transact-SQL (2012)

get 3 random numbers that don't match

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2013-05-12 : 22:49:52

What is the quickest way to get 3 numbers between 1 and a 100 that are unique? One of the numbers may not occur twice

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-13 : 00:52:28

--May be this?
SELECT ABS(CAST(NEWID() AS binary(6)) %100) + 1, ABS(CAST(NEWID() AS binary(6)) %100) ,ABS(CAST(NEWID() AS binary(6)) %100) + 2

Refer this link...
http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 00:52:35

[code]
SELECT TOP 3 number
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND 100
ORDER BY NEWID()
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ddamico
Yak Posting Veteran

76 Posts

Posted - 2013-05-13 : 10:01:53

We are using a slighly different approach we have created a .Net CLR Table Valued Function that takes starting Key and Total number of Keys and returns a random number between 0 and 1 decimal of course sequenced starting "Key". I have run it with requests upwards of 20 million and it runs in about 2 minutes and is pretty random.

Its a great solution as SQL Server random function isn't really random and there is a lot of coding necessary to achieve a very random value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources