August 14, 2008 at 8:36 am
Dear all
I need your help on this ..How to generate 4 digit random
number without using rand(),newid() funtion..
one more thing the random generated number it should not
repeate again
Thanks
chandru.
August 14, 2008 at 9:02 am
generate a random number without using the existing tools of rand() and newid()?
why would handicap yourself?
what is the real requirement, or what are you trying to do? the only way other than the above would be to use something that was time based, i would reckon...unless you are going to try and write your own rand() function, which would be kinda silly, right?
Lowell
August 14, 2008 at 9:07 am
There isn't a way, in SQL, to generate a non-repeating random number, following the constraints you've called for. You can sort of do it with newid(), but you've already said you don't want to use that.
So, I recommend rolling dice and typing the numbers generated into a column with a unique index on it. 4 10-sided dice (like the kind used in role-playing games), will give you what you need. If negative numbers are acceptable, use another die (different color than the rest or different number of sides), odd = negative, even = positive.
Truth be told, this sounds like an interview or exam question, because of the silly constraints on it, and you should simply tell the interviewer or examiner that you don't know of a way to do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 9:07 am
I agree with Lowell, I know lots of ways to write a pseudo-random number generator but I am not going to bother until I understand what the real constraints are here. Explicitly:
1. What will you be using it for...
1a. (ie., How "random" does it really have to be?)
2. Why can't you use the built-in functions?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 9:14 am
Chandru (8/14/2008)
one more thing the random generated number it should notrepeate again
As Gus alluded to, this constraint is at best ambiguous and at worst, just plain wrong.
For instance: there are only 10,000 4-digit numbers. Any generator is going to have to repeat a number by the 10,000th call.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 12:31 pm
rbarryyoung (8/14/2008)
Chandru (8/14/2008)
one more thing the random generated number it should notrepeate again
As Gus alluded to, this constraint is at best ambiguous and at worst, just plain wrong.
For instance: there are only 10,000 4-digit numbers. Any generator is going to have to repeat a number by the 10,000th call.
Actually, it depends on what you define as a valid number. If, for example, you use a base-95 counting system, and allow negatives, you can get quite a few more than 10-thousand numbers. You get a range from -~~~~ to +~~~~ (using the standard keyboard characters from the ASCII set, without using DEL and such, since DEL can't be stored in the char data type).
Even with that, you would get a finite set of non-repeating numbers. 95 to the 8th power, minus 1, to be precise. That's 6,634,204,312,890,624 distinct numbers, in decimal counting. Not as many as BigInt, but quite a few more than Int, and for only 1 byte more of storage.
However, I seriously doubt that pertains to the original post. Fun trivia, though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 12:34 pm
Good point. I'll remember to reference my base arithmetic system in the future :).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 2:06 pm
SELECTABS(CHECKSUM(GETDATE())) % 10000
N 56°04'39.16"
E 12°55'05.25"
August 14, 2008 at 2:58 pm
I was just kidding with previous post.
But here is a way to get 10,000 unique nonrepeating random numbers between 0000 and 9999
In this example, I have set to environment to 100 random values only.
But it still fulfills all requirements
1) No use of RAND() nor NEWID() or NEWSEQUENTIALID()
2) The number is unique and nonrepeating
SET NOCOUNT ON
DECLARE@random TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
i CHAR(2)
)
DECLARE@RowID INT
SET@RowID = 0
WHILE @RowID < 100
BEGIN
INSERT@random
SELECT@RowID,
REPLACE(STR(@RowID, 2, 0), ' ', '0')
SET@RowID = @RowID + 1
END
SELECT@RowID = 0
WHILE EXISTS (SELECT * FROM @random)
BEGIN
SELECT@RowID = ABS(CHECKSUM(CONVERT(CHAR(21), GETDATE(), 121) + CAST(COUNT(*) AS VARCHAR(11)))) % COUNT(*)
FROM@random
DELETE
FROM@random
OUTPUTdeleted.i
WHERERowID = @RowID
UPDATE@random
SETRowID = RowID - 1
WHERERowID > @RowID
END
Enjoy!
N 56°04'39.16"
E 12°55'05.25"
August 14, 2008 at 3:38 pm
The numbers aren't random. The sequence is semi-random, but the numbers are 0-99, inclusive.
If you really want 0-99 in a semi-random sequence, it would be much easier to do this:
select number
from dbo.numbers
where number between 0 and 99
order by checksum(cast(number as varchar(2)) + '000')
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 3:42 pm
Peso (8/14/2008)
But here is a way to get 10,000 unique nonrepeating random numbers between 0000 and 9999
Yes, that is not hard at all. But it is not what the OP asked for, they asked for random numbers between 0000 and 9999 that would "not repeat again." That is, unlimited random numbers that would never repeat.
Since that is clearly impossible, It seems best to wait for clarification (which probably won't come because I think that Gus was right in the first place about why they were asking).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 3:52 pm
OP never wrote "unlimited" number of random records.
He wrote he needed random records with 4 characters in length that would never [from the original set] repeat.
N 56°04'39.16"
E 12°55'05.25"
August 14, 2008 at 4:10 pm
GSquared (8/14/2008)
If you really want 0-99 in a semi-random sequence, it would be much easier to do this:
select number
from dbo.numbers
where number between 0 and 99
order by checksum(cast(number as varchar(2)) + '000')
Yes, they are in semi-random sequence, but the sequence are always the same!
Not until you hit number greater than 55000 you can get different order of sequence, because then the CHECKSUM function tends to fail due to internal implementation.
See here how CHECKSUM is built http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832
Attached are 5-digit numbers that produce the same checksum with your algorithm above.
There are 800 pairs of 5-digit numbers producing the same checksum value.
800 out of 90000 is less than 1% so the chance the sequence list of random number changes are slim.
N 56°04'39.16"
E 12°55'05.25"
August 15, 2008 at 6:41 am
So add getdate or @@dbts or some such to the checksum. Then you get a different sequence each time.
Still simpler than the loops and such.
And still doesn't actually do what the OP asked for. Just like yours, it's not random numbers, it's pre-determined numbers in a pseudo-random sequence.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2008 at 9:37 am
Peso (8/14/2008)
OP never wrote "unlimited" number of random records.He wrote he needed random records with 4 characters in length that would never [from the original set] repeat.
Fair enough. My apologies.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply