How Many Different Random Numbers?

Question

How Many Different Random Numbers?

amitgoswamiipec

SSC Rookie

Points: 25
More actions
September 29, 2014 at 9:57 pm

#299692

Comments posted to this topic are about the item How Many Different Random Numbers?

Viewing 15 posts - 1 through 15 (of 60 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29387 More actions · Answer 1

I think the answer to this is so wrong, it is ridiculous.

The correct answer is that there could be up to 20 different values(assuming that the answer required is the number of distinct values returned, that is). The SELECT statement does not have an ORDER BY in it so the records uses from table sys.columns could be for columns in 1 table or 20 tables or, indeed, any number of tables between 1 and 20.

With the SELECT statement as it stands, you get a different result depending on, for instance, which database you run it in. I suspect that you may even get a different result depending on what SQL is doing (for the same reason that SQL may return records in different orders depending on how the query is actually executed)

The issue is that the seed being used is based on the (object) id in syscolumns. This table contains the columns defined in views, tables, sprocs. If you include column ID (and maybe even Object_name(ID) and name) in the columns returned, you start to get an idea of what the RANK function is actually returning.

The only way I get even close to the "suggested" answer is by adding "ORDER BY ID" to the query. This works because SQL Server sysobject objects have low values for the ID column and hence the order by would result in the first 20 records being for "sysrscols" and sysrowsets which have 13 and 18 columns respectively. By ordering by ID, you would end up with 2 values BUT that is not what the query in the question actually does.

mukeshkane SSC-Addicted Points: 448 More actions · Answer 2

I agree, the options are incorrect. Because when I run this on sql server 2005 it gives me three distinct random numbers

Hany Helmy SSChampion Points: 13921 More actions · Answer 3

Tested this in 2008 R2 & 2012, recieved 2 distinct values in 20 rows, the answer is correct, I don`t see any issue here folks 😎

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Roland C Hall of Fame Points: 3473 More actions · Answer 4

On 2012 :

on master database, 6 different values, on other databases 2 differents values :doze:

ross.chang SSC Journeyman Points: 82 More actions · Answer 5

I got a distinct of 6 different values from SQL Server 2012:

0.713591993212924

0.713647892126698

0.713722424011731

0.713852854810538

0.713871487781797

0.713908753724313

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

happycat59 (9/29/2014)
With the SELECT statement as it stands, you get a different result depending on, for instance, which database you run it in.

+1

qqliukk SSChasing Mays Points: 605 More actions · Answer 7

yes, I got 6 different values by distinct key word.

different version database got diff values.

so the answer is incorrect.

no stander answer I think so.

my test ENV:

Microsoft SQL Server Management Studio11.0.5058.0

Microsoft Analysis Services Client Tools11.0.5058.0

Microsoft Data Access Components (MDAC)6.1.7601.17514

Microsoft MSXML3.0 6.0

Microsoft Internet Explorer9.10.9200.17089

Microsoft .NET Framework4.0.30319.18444

Operating System6.1.7601

search results:

0.713591993212924-1073624922

0.713647892126698-1072815163

0.713722424011731-1072372588

0.713852854810538-1070913306

0.713871487781797-1070573756

0.713908753724313-1068897509

search script as below:

select distinct * from

( SELECT TOP 20 RAND(RANK() OVER ( ORDER BY id )) as rand_rank, id FROM sys.syscolumns AS s) tab

please kindly review it and let me know if have any issues.

thank you very much.

have a nice weekend!

thanks,

ke liu

thanks & best regards,
ke liu
86+15011472596
qq: 421190916
email: qqliukk@hotmail.com
河南省周口市商水县袁老乡第二初级中学教师公寓3号

RKaram SSCommitted Points: 1518 More actions · Answer 8

I run the query and I got the below result :

DATABASE Name : Master

-----------------------------

0.713591993212924

0.713647892126698

0.713722424011731

0.713852854810538

0.713871487781797

0.713908753724313

and if i run it on other DB I ll get the below result

0.713591993212924

0.713852854810538

so in the question we should indicate which DB we should select

have a nice day,

Rabih

rkaram

Stuart Davies SSCoach Points: 19505 More actions · Answer 9

Although there is a non-stated rule here that the most recent version of SQL is used, unless a different version is specified, the answer to this question not only appears to be version specific but also database specific.

Nice idea for a QOTD but not executed as well as it could have been.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Carlo Romagnano SSC-Insane Points: 22599 More actions · Answer 10

rabih_karam (9/30/2014)
I run the query and I got the below result :
DATABASE Name : Master
-----------------------------
0.713591993212924
0.713591993212924
0.713591993212924
0.713647892126698
0.713647892126698
0.713647892126698
0.713647892126698
0.713722424011731
0.713722424011731
0.713722424011731
0.713722424011731
0.713722424011731
0.713722424011731
0.713722424011731
0.713852854810538
0.713871487781797
0.713871487781797
0.713908753724313
0.713908753724313
0.713908753724313
and if i run it on other DB I ll get the below result
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713591993212924
0.713852854810538
0.713852854810538
0.713852854810538
0.713852854810538
0.713852854810538
0.713852854810538
so in the question we should indicate which DB we should select

That's true!

id is the object_id of the table.

So, the answer depends on the numbers of columns of the tables and the order in which they are returned.

Last, adding an order by name the query, most probably, returns 20 different value:

SELECT TOP 20

RAND(RANK() OVER ( ORDER BY id ))

FROM

sys.syscolumns AS s

ORDER BY name

rosman 91334 SSC Enthusiast Points: 116 More actions · Answer 11

I received 20 different results:

0,728312040506902

0,728349306449418

0,728386572391934

0,728461104276967

0,728796497759615

0,728833763702131

0,728889662615906

0,729374119868619

0,729784045236299

0,72998900792014

0,730697060827951

0,732020001787283

0,733529272459198

0,734442288050849

0,734796314504755

0,737237233739579

0,738168882302489

0,740684333422346

0,741224689588834

0,743292949398494

Using SQL Server 2012 and a rather large syscolumns table (4153 rows). I believe the answer is therefore incorrect.

robertjtjones SSC-Addicted Points: 484 More actions · Answer 12

Agree with most above points - it depends on the database.

I also think a better explanation is required as

The reason is that when we generate a RANK of top 20 ids in syscolumns, then there are 2 distinct number will generate

is tantamount to saying - it does this because it does.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 13

Sorry but a Cr@ppy question with a bad answer...

Depending on version and database will depend on the result you will get....

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 14

Thank you for the question Amit.

Given options of 0,2 and 20 with the set's cardinality of 20 makes this kind of obvious; the granularity of syscolumns is {object,column}, one can rule out 0 as the query will return values, one can also rule out twenty as most objects have more than one column which leaves 2 as the only applicable answer.

😎