Magic 8 Ball

  • eccentricDBA

    SSCarpal Tunnel

    Points: 4844

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Well that was fun. Thanks.

  • andrew_dale

    Ten Centuries

    Points: 1200

    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.

  • eccentricDBA

    SSCarpal Tunnel

    Points: 4844

    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.

  • Brian J. Parker

    SSC Eights!

    Points: 879

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

  • David in .AU

    SSCommitted

    Points: 1784

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

  • andrew_dale

    Ten Centuries

    Points: 1200

    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.

  • eccentricDBA

    SSCarpal Tunnel

    Points: 4844

    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.

  • eccentricDBA

    SSCarpal Tunnel

    Points: 4844

    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.

  • andrew_dale

    Ten Centuries

    Points: 1200

    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

  • eccentricDBA

    SSCarpal Tunnel

    Points: 4844

    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

    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

  • j-1064772

    SSCertifiable

    Points: 5316

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    This is just as good the second time around.

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

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