September 30, 2014 at 8:38 am
Clearly a broken question. I'm not sure why this worked for me in a few tests, but I only got two results in 2012 and 2014. I suspect I have a default master db and some luck.
Points have been awarded back and the question marked as problematic.
A nice discussion here on the way this works, however. Thanks for that.
September 30, 2014 at 9:02 am
Steve Jones - SSC Editor (9/30/2014)
A nice discussion here on the way this works, however. Thanks for that.
I second that opinion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 30, 2014 at 9:06 am
Thanks for an interesting question, Amit. I immediately thought that 20 would be the correct answer (which shows my incomplete understanding of syscolumns, I suppose), and was shocked to see 2 as the correct answer. When I tried running the code (thinking to myself "that can't be right!"), I initially saw 2 distinct values, but I also saw differing values in different databases.
Even though this question is "problematic", as Steve Jones said, I did enjoy reading the discussion about how it works. Amit, don't let the discussion dissuade you from trying another QotD.
September 30, 2014 at 9:38 am
I get 3 distinct random numbers on my dev database and 6 distinct random numbers using the master database. I am using Sql Server 2005.
September 30, 2014 at 9:44 am
Sean Lange (9/30/2014)
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.
It does matter when you're selecting the top 20, because the order determines which 20 you get. The inner ORDER BY (in the RANK function) doesn't guarantee the outer order. But I agree that 2 is the most likely or least incorrect, depending on which way you look at it.
I got the question wrong because I didn't stop to consider that the id column might not be unique.
John
September 30, 2014 at 9:56 am
John Mitchell-245523 (9/30/2014)
Sean Lange (9/30/2014)
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.
It does matter when you're selecting the top 20, because the order determines which 20 you get. The inner ORDER BY (in the RANK function) doesn't guarantee the outer order. But I agree that 2 is the most likely or least incorrect, depending on which way you look at it.
I got the question wrong because I didn't stop to consider that the id column might not be unique.
John
I believe we are saying the same here John. Of course the RANK function does not determine the order of the output. 😉
_______________________________________________________________
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/
September 30, 2014 at 10:08 am
I looked at the question and was off to figure it out. I actually ended up running the query as it was written and got 20 results which showed six unique values. I doulbe checked this by running this
select Top 20
RAND(RANK() OVER ( ORDER BY id )) as fred
into #myTempTable2
FROM sys.syscolumns AS s
select distinct(fred)
from #myTempTable2
This gave me 6 numbers. But I know that QotD's are tricky so I looked at the total results and saw of the 20 returned values in detail there was only one that was truly unique. All other were repeated at least once. Understanding the definition of Rand to me the answer was that there were 20 results but only one was random, or at least not repeating. I then looked at the question where possible answers were given and found 1 was not an option.
Reading further there was the explaination of the question and that it was problematic. So I took the answer of 2 just to read the discussion to see if others got where I did.
M.
Not all gray hairs are Dinosaurs!
September 30, 2014 at 10:10 am
TomThomson (9/30/2014)
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.
Sounds like a contradiction, an event which in it self cannot be considered random just because we do not comprehend the causes, resulting in another event (for the lack of better word) in which we have no comprehension of the cause, that's not random, that's an IBM Billing System:-D
😎
September 30, 2014 at 1:30 pm
Eirikur Eiriksson (9/30/2014)
Sounds like a contradiction, an event which in it self cannot be considered random just because we do not comprehend the causes, resulting in another event (for the lack of better word) in which we have no comprehension of the cause, that's not random, that's an IBM Billing System:-D😎
Or possibly a Microsoft SQL Server Licensing system?
Tom
September 30, 2014 at 11:30 pm
SQL Server 2012 R2 ouput:
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
(20 row(s) affected)
October 1, 2014 at 7:41 am
Tested on SQL 2005 and SQL 2008
If run against Master: 6 distinct values returned
If run against any other database: 2 distinct values are returned.
The question would therefor be invalid due to ambiguity.
October 2, 2014 at 2:20 am
Great discussion! 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 5, 2014 at 6:56 am
I did not understand the relevance of this question if the results are varying across version and diff SQL Instances. My results came in 20. So I selected 20.
Can you explain why the correct answer choice is 2??
Thanks.
October 5, 2014 at 7:10 am
SQL-DBA-01 (10/5/2014)Can you explain why the correct answer choice is 2??
The way this site works is that you need to give an ansnwer to get access to the explanation and discussion, and that it is not possible to set a question to either give nobody a point or give anyone a point regardless of answer. And there are people on this site who seem to think that those point are worth anything, so they can get quite vocal if they feel that they missed a point they "deserved".
So Steve basically said in the comment above the question: "there is no truly correct answer, but if you just say that the answer is 2 you will get a point and gain access to the discussion".
October 5, 2014 at 11:29 am
TomThomson (9/30/2014)
Eirikur Eiriksson (9/30/2014)
Sounds like a contradiction, an event which in it self cannot be considered random just because we do not comprehend the causes, resulting in another event (for the lack of better word) in which we have no comprehension of the cause, that's not random, that's an IBM Billing System:-D😎
Or possibly a Microsoft SQL Server Licensing system?
Nah, even the foremost Chaos theory experts shy away from that one:crazy:
😎
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply