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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -