Just a day ago I received email from reader how to get single random number for range of rows of any table. The question was not very clear to me so I had asked him to send me question in simpler words. He sent me question back in simple words. Let us understand this problem using database AdventureWorks.
In AdventureWorks database we have table called Person.Address. How to get single random number generated for PostalCode ‘98011’ and another single random number for PostalCode ‘98033’. So far I have never received scenario like this. I had previously faced situation where I had to genrate new random number for each row of table.
Let us understand two scenarios here.
Different Random Number for Different Rows (Using NEWID())
USE AdventureWorks
GO
SELECT TOP 5 NEWID() AS GUID, *
FROM Person.Address
GO
Single Random Number for Set of Rows (Using RAND())
USE AdventureWorks
GO
SELECT TOP 5 RAND() AS RandomNumber, *
FROM Person.Address
WHERE PostalCode = '98011'
GO
SELECT TOP 5 RAND() AS RandomNumber, *
FROM Person.Address
WHERE PostalCode = '98033'
GO
Watch a 60 second video on this subject
[youtube=http://www.youtube.com/watch?v=1d29ka0hHnc]I have sent email back to my reader asking his need for single random number for set of rows in real world example but I have not received his reply. I want to bring this to my readers and ask them what do you think can be usage of this concept in real life application.
Reference : Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
I use it a lot. Mainly for checking the integrity of my data mapping before it hits production on my deployments.
The way that the RAND() funciton works, as long as the unique value you want a distinct random number for is an integer, you can do something like:
select
rand(PostalCode) as RandomNumber, PostalCode
from
Person.Address
where
StateProvinceID = 79
order by
PostalCode
The RAND() function will only return one unique value for each seed, so if you pass it a common seed all of those rows would share the same random value.
I will guess it depends on what he’s doing. :)
It maybe, he’s exploiting the random number for session management or presenting the result in some format in front-end/reports or working with some existing or new design pattern that requires it.
One way of looking at the request is:
Say there are 5 records with PostalCode=98011. The reader may want to retrieve any one of these 5 records when running a query. Next time the query is run, he may want to retrieve another random record with same PostalCode
?
I like to generate a random number for primary key, so I can get one of the ads randomly to come up for a page.
I think this would be a real life scenario where its needed.
Let’s say I want to check random records from my dataset and I want exactly 100 records returned. Well I would run the following: SELECT TOP 100 FROM TABLE_NAME ORDER BY NEWID()
I dont want just the first top 100, I want 100 random records and I am not trying to generate a new GUID. Which is why I use it in the ORDER BY clause. It works good for my purposes!
@Samson Loo
Great Tip! thanks!
While trying to find an answer for the Scenario: Single Random Number for Set of Rows (Using RAND()), my use of this would be to ~~equally cut a table into groups, for example: cut 20000 records into 7 days or 28 days. So each row will get a random number between 1,7 so you have 7 groups.
Solved it like this (using one of your random creation scripts from above):
declare @cursor cursor, @userID int
set @cursor = cursor for
(select id
from users)
open @cursor
while 1=1
begin
fetch from @cursor into @userID;
if @@fetch_status 0
break
update users set reportMonthDay = 1 + CONVERT(INT, 28*RAND())
where id=@userID
end
how to random record from database(Sql Server) table each time when i am executing the Query, i want to use it to Quiz application
HELP ME OUT FOR A SQL 2005 QUERY TO FIND ROW NO THE RECORD
VIZ . RAJESH EXSIST ON ROW NO 5