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.

Solarwinds

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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Download Cumulative update package 3 for SQL Server 2008
Next Post
SQLAuthority News – Microsoft Certification Exam – Discount Code – Free Second Chance – MCTS, MCITP, MCPD

Related Posts

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.

    Reply
  • Dan G. Switzer, II
    January 28, 2009 8:08 pm

    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.

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

    Reply
  • 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

    ?

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

    Reply
  • 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!

    Reply
  • @Samson Loo

    Great Tip! thanks!

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

    Reply
    • 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

      Reply
  • 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

    Reply
  • HELP ME OUT FOR A SQL 2005 QUERY TO FIND ROW NO THE RECORD
    VIZ . RAJESH EXSIST ON ROW NO 5

    Reply

Leave a Reply

Menu