SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 get 3 random numbers that don't match
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madlo
Starting Member

25 Posts

Posted - 05/12/2013 :  22:49:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/13/2013 :  00:52:28  Show Profile  Reply with Quote
--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

India
52317 Posts

Posted - 05/13/2013 :  00:52:35  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ddamico
Yak Posting Veteran

76 Posts

Posted - 05/13/2013 :  10:01:53  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000