locked
Generate Random Row RRS feed

  • Question

  • I'm not sure if this is where my question belongs, so I apologize if this is completely of another ballpark to the forum.

    I'm just trying to retrieve a random record from one of my tables.  I have two problems here: when I try to use LIMIT 1 after ORDER BY RAND() I get syntax errors for whatever reason (Incorrect syntax near 'LIMIT'.) OR if I use the SELECT TOP 1 * approach, I keep getting the same answer.  Matter of fact, when I just run the RAND(), I always get the rows in the same order.  I'm not heavy into SQL, so am I missing something simple here?

    Here is my SQL code:

    SELECT     QuestionID, Category, Question, ExplanationQ, CorrectAnswer, ExplanationAnswer
    FROM         WA_Questions
    WHERE     (Category = 'TenCommandments')
    ORDER BY RAND()


    My results always come back in the order of:
    Commandment1
    Commandment10
    Commandment2
    Commandment3
    .
    .
    .
    Commandment9
    Wednesday, April 8, 2009 8:09 AM

Answers

  • Instead of rand() use NewId()

    SELECT     QuestionID, Category, Question, ExplanationQ, CorrectAnswer, ExplanationAnswer
    FROM         WA_Questions
    WHERE     (Category = 'TenCommandments')
    ORDER BY newid()

    Mangal Pardeshi
    SQL With Mangal
    • Proposed as answer by Mangal Pardeshi Thursday, April 9, 2009 6:09 AM
    • Marked as answer by Lil_ Quaty Thursday, April 9, 2009 6:21 PM
    Thursday, April 9, 2009 6:09 AM

All replies

  • Instead of rand() use NewId()

    SELECT     QuestionID, Category, Question, ExplanationQ, CorrectAnswer, ExplanationAnswer
    FROM         WA_Questions
    WHERE     (Category = 'TenCommandments')
    ORDER BY newid()

    Mangal Pardeshi
    SQL With Mangal
    • Proposed as answer by Mangal Pardeshi Thursday, April 9, 2009 6:09 AM
    • Marked as answer by Lil_ Quaty Thursday, April 9, 2009 6:21 PM
    Thursday, April 9, 2009 6:09 AM
  • Wow, go figure it works.  All I had to do then in order to only pull back one row was use the TOP command after SELECT.  This is my now working code:

    SELECT     TOP (1) QuestionID, Category, Question, ExplanationQ, CorrectAnswer, ExplanationAnswer
    FROM         WA_Questions
    WHERE     (Category = 'TenCommandments')
    ORDER BY NEWID()

    Can you explain to me why the RAND() function wasn't doing that??  Or the fact of why I can't use LIMIT in either of the cases: ORDER BY NEWID() or ORDER BY RAND()

    But thank you so far for helping me to figure this out!  I didn't know I could use the NEWID()!
    Thursday, April 9, 2009 6:21 PM