Magic 8 Ball

• Comments posted to this topic are about the item Magic 8 Ball

• Well that was fun. Thanks.

• 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.

• 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.

• But I don't want to "consult the oracle"... I want to consult the SQL Server. *rimshot* :rolleyes:

• 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()?

• 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.

• 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.

• 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 79`

I 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.

• 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

• andrew_dale (6/10/2015)

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

Yes.

Here is the example I promised.

`SELECT Answer_Text,ABS(CHECKSUM(NEWID()) % 100) FROM @Magic8BallAnswers ORDER BY 2`

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

Better not tell you now 11

You may rely on it 17

Outlook good 18

Yes 41

Most likely 41

Without a doubt 59

Don't count on it 62

Cannot predict now 70

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

Yes definitely 99

• 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 ! 😀

• This is just as good the second time around.

Viewing 13 posts - 1 through 12 (of 12 total)