SQL SERVER – Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader

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

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 (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader

  1. 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.

  2. 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.

  3. 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

    ?

  4. 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.

  5. 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!

  6. 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

  7. 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

  8. Pingback: SQL SERVER – Retrieving Random Rows from Table Using NEWID() « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – Generate Random Values – SQL in Sixty Seconds #042 – Video « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s