May 20, 2015 at 3:27 pm
Comments posted to this topic are about the item Magic 8 Ball
June 9, 2015 at 7:10 am
Well that was fun. Thanks.
June 9, 2015 at 9:18 am
how does that ORDER BY work?
I assumed the function would produce a number between 0 and 99 and then interpret that as a column number in the query, but there are only 3 columns.
June 9, 2015 at 11:48 am
Yes.
The NEWID() generates a random GUID. Then we do a checksum on against the GUID to get us integer value. Next we do a modulo operation to get the remained when we divide the value by 100. Using the ABS function we insure that the value is positive. Which gives us a random number between 0 and 99.
DECLARE @newGUID uniqueidentifier
DECLARE @checksum int
DECLARE @abs int
SET @newGUID = NEWID();
PRINT @newGUID
SET @checksum = CHECKSUM(@newGUID)
PRINT @checksum
PRINT @checksum % 100
SET @abs = ABS(@checksum % 100)
PRINT @abs
-------------------------------------------------------------------
7BF73759-8D15-4628-BABD-5DB223ECBA85
-1335228179
-79
79
Updated:
When called in the order by the functions generates a random value for the row. I use the top 1 with the randomness to return one random value from the table.
June 9, 2015 at 3:11 pm
But I don't want to "consult the oracle"... I want to consult the SQL Server. *rimshot* :rolleyes:
June 9, 2015 at 8:26 pm
A bit of fun.
Question:
Since I assume the computer your SQL Server is on has a network card, why not just order by newid()?
June 10, 2015 at 1:58 am
That doesn't answer my question. As I said the function will produce a number between 0 and 99, as you have just confirmed.
So how does ORDER BY 79 actually work as there is no 79th column?
In fact if I replace the function in the ORDER BY with 79
SELECT TOP 1 @Answer = Answer_Text FROM @Magic8BallAnswers ORDER BY 79
I get an error
The ORDER BY position number 79 is out of range of the number of items in the select list.
June 10, 2015 at 4:26 am
David in .AU (6/9/2015)
A bit of fun.Question:
Since I assume the computer your SQL Server is on has a network card, why not just order by newid()?
Yes the newid() would have been enough. I was playing with a random number generator first and decided to use it to create the magic 8 ball.
June 10, 2015 at 4:33 am
andrew_dale (6/10/2015)
That doesn't answer my question. As I said the function will produce a number between 0 and 99, as you have just confirmed.So how does ORDER BY 79 actually work as there is no 79th column?
In fact if I replace the function in the ORDER BY with 79
SELECT TOP 1 @Answer = Answer_Text FROM @Magic8BallAnswers ORDER BY 79I get an error
The ORDER BY position number 79 is out of range of the number of items in the select list.
OK I understand now. It's not a static number. The calculation changes for each row. The easiest way to see that is to get rid of the top 1 and the function as your third column and change the order by to order by 3. I'll post the example later today here.
June 10, 2015 at 4:36 am
yes that now makes sense
the function ABS(CHECKSUM(NEWID()) % 100) is being treated as a field even though it is not in the select list
Each row gets a value (may be different may be the same as the range is only 0 to 99) and then that value is used to order the list
June 10, 2015 at 10:48 am
andrew_dale (6/10/2015)
yes that now makes sensethe function ABS(CHECKSUM(NEWID()) % 100) is being treated as a field even though it is not in the select list
Each row gets a value (may be different may be the same as the range is only 0 to 99) and then that value is used to order the list
Yes.
Here is the example I promised.
SELECT Answer_Text,ABS(CHECKSUM(NEWID()) % 100) FROM @Magic8BallAnswers ORDER BY 2
Answer_Text
---------------------------------------------------------------------------------------------------- -----------
Better not tell you now 11
You may rely on it 17
Outlook good 18
Yes 41
Most likely 41
Concentrate and ask again 51
Without a doubt 59
Ask again later 61
Don't count on it 62
Cannot predict now 70
Reply hazy try again 72
As I see it, yes 76
My sources say no 77
Signs point to yes 80
It is decidedly so 81
It is certain 82
Very doubtful 85
Outlook not so good 90
My reply is no 92
Yes definitely 99
May 30, 2016 at 8:12 am
Brian J. Parker (6/9/2015)
But I don't want to "consult the oracle"... I want to consult the SQL Server. *rimshot* :rolleyes:
Hilarous ! 😀
September 6, 2016 at 7:19 am
This is just as good the second time around.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply