How Many Different Random Numbers?

Question

How Many Different Random Numbers?

Viewing 15 posts - 31 through 45 (of 60 total)

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

Tom_Sacramento SSCertifiable Points: 6270 More actions · Answer 1

The answer should be 20

Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

Todd Reddinger Ten Centuries Points: 1362 More actions · Answer 2

the ORDER BY in the OVER clause orders the records returned by the select. By adding an order by on the select, a differect set of records is returned for the ORDER BY in the OVER clause to order.

SELECT TOP 20

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

FROM

sys.syscolumns AS s order by s.colid

Thanks,

ToddR

SqlMel SSCrazy Points: 2901 More actions · Answer 3

Todd Reddinger (9/30/2014)
the ORDER BY in the OVER clause orders the records returned by the select. By adding an order by on the select, a differect set of records is returned for the ORDER BY in the OVER clause to order.
SELECT TOP 20
RAND(RANK() OVER ( ORDER BY id ))
FROM
sys.syscolumns AS s order by s.colid

That's true.

---------------
Mel. 😎

scintillaspire SSC-Addicted Points: 466 More actions · Answer 4

ross.chang (9/30/2014)
I got a distinct of 6 different values from SQL Server 2012:
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

+1

Nicholas Cross Mr or Mrs. 500 Points: 543 More actions · Answer 5

Poor question. I only got this right by eliminating the impossible (0) and the extremely unlikely (20), thus leaving 2. However, the explanation as to why 2 is the correct answer doesn't make sense and shows that the question setter has not actually thought through the question properly.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

September 30, 2014 at 7:26 am

#1749130

Thanks for the question.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

After reading the comments I guess I was lucky to get the right answer.

andrew.ing SSC Eights! Points: 853 More actions · Answer 8

Todd Reddinger (9/30/2014)
the ORDER BY in the OVER clause orders the records returned by the select. By adding an order by on the select, a differect set of records is returned for the ORDER BY in the OVER clause to order.
SELECT TOP 20
RAND(RANK() OVER ( ORDER BY id ))
FROM
sys.syscolumns AS s order by s.colid

In practice the OVER clause often does order the rows returned, in the above it is the only sorting that is done. But is that guaranteed? What would happen if there were two OVER clauses:

SELECT

id,

RANK() OVER (ORDER BY id ASC) AS RankAsc

,RANK() OVER (ORDER BY id DESC) AS RankDesc

FROM

sys.syscolumns AS s

Would the first or the second OVER BY clause define the SELECT order? On my test, id is sorted in descending order.

I'd say that the ordering is a side effect of calculating the RANK function, and that the order of the SELECT is undefined without an ORDER BY clause on the SELECT itself.

RK Mandava SSCarpal Tunnel Points: 4153 More actions · Answer 9

We will get up to 20 different values depends on the database and sql version. So the answer should be 20

However, the explanation as to why 2 is the correct answer doesn't make sense and shows that the question setter has not actually thought through the question properly.

Aleksl-294755 Hall of Fame Points: 3514 More actions · Answer 10

RK Mandava (9/30/2014)
We will get up to 20 different values depends on the database and sql version. So the answer should be 20
However, the explanation as to why 2 is the correct answer doesn't make sense and shows that the question setter has not actually thought through the question properly.

+1

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

andrew.ing (9/30/2014)
Todd Reddinger (9/30/2014)
the ORDER BY in the OVER clause orders the records returned by the select. By adding an order by on the select, a differect set of records is returned for the ORDER BY in the OVER clause to order.
SELECT TOP 20
RAND(RANK() OVER ( ORDER BY id ))
FROM
sys.syscolumns AS s order by s.colid
In practice the OVER clause often does order the rows returned, in the above it is the only sorting that is done. But is that guaranteed? What would happen if there were two OVER clauses:
SELECT
id,
RANK() OVER (ORDER BY id ASC) AS RankAsc
,RANK() OVER (ORDER BY id DESC) AS RankDesc
FROM
sys.syscolumns AS s
Would the first or the second OVER BY clause define the SELECT order? On my test, id is sorted in descending order.
I'd say that the ordering is a side effect of calculating the RANK function, and that the order of the SELECT is undefined without an ORDER BY clause on the SELECT itself.

Andrew you are absolutely correct. The actual output has no defined order. However, the RANK function is what is defining the seed for the RAND function. It doesn't actually matter what order the results are, the question didn't ask anything about what order they are in. It asked how many distinct values would be returned.

I thought it was an interesting question but not thought out completely. I figured the least incorrect choice was 2 so went with that and got lucky.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

andrew.ing SSC Eights! Points: 853 More actions · Answer 12

andrew.ing

SSC Eights!

Points: 853

September 30, 2014 at 7:54 am

#1749153

Thanks Sean, the penny has dropped now.

below86 SSChampion Points: 11528 More actions · Answer 13

ross.chang (9/30/2014)
I got a distinct of 6 different values from SQL Server 2012:
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

I got the same thing.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

TomThomson SSC Guru Points: 104773 More actions · Answer 14

Good try at an interesting trick question. I guessed the point was how many distinct object ids would be in the first 20 rows selected from sys.syscolumns, there will be at least one and it's extremely unlike that there will be 20, so if that's what the question meant 2 was the only possible answer. But that certainly not what the question asked, because the wording doesnt actually ask that.

The wording doesn't work, because (a) the number of distinct values returned varies according to the database it's running in - in my installation of SQLS 2014 it is 6 for MASTER, 3 for TEMPDB, and 2 for MODEL and probably everything else (those who think it should be 6 deserve kicking for poor security if they are running SQL 2014, as that means that MASTER is their default database) and (b) if the values are actually random the only possible correct answer is 20 (a collection of random numbers can contain repeats) and (c) if they aren't actually random the only correct answer is 0.

Clearly b and c together prove that the answer can't be anything other than 0 or 20, and (a) is just icing on the cake (as is any variation between releases of SQL server - I haven't looked to see if there is any).

Speaking from a strictly pedantic point of veiew, it's at the least debatable whether any random numbers are returned - certainly the number returned by RAND with a seed is not a random number unless the seed is itself random, and I don't think that the top 20 ranks of the object identifiers of column owning objects are random numbers. So that suggests that 0 is the nearest answer to correct.

However, I chose 2 because I wanted to get the points :w00t:, not the right answer. Looks like a QotD that encourages illogical reasoning :hehe:.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 15

Stewart "Arturius" Campbell (9/30/2014)
Ed Wagner (9/30/2014)
I don't mean to spark a mathematical debate here, but what is truly random anyway? 😉
Randomness leads to unpredicatable outcomes, very much as depicted in Chaos Theory[/url]
e.g. how a solar flare in Ursa Minor could lead to a nova in Andromeda, or a butterfly flapping it's wings in New Mexico leads to a hurricane in China...

Maybe random is not what is predicted by chaos theory, - perhaps chaos theory demonstrates that some results that appear to be random are actually just chaotic deterministic results and not random at all. The trouble with suggesting that what is depicted in chaos theory is random is that it would require us to say that as computational efficiency and our power to observe all factors improve some things that were previously random cease to be random. Currently the geneerally accepted understanding of physics suggests that there is genuine randomness that can't possibly be eliminated by better observation and computation, and there are chunks of mathematics and statistics that are based on that view, so randomness is quite different from anything predicted by chaos theory. So unless you believe in some "hidden-variable" (ie as yet undiscovered dependency) version of quantum dynamics (which would make you an out and out crackpot so far as most scientists and mathematicians are concerned) you can't believe that randomness is what chaos theory predicts.

Tom