SQL Random selection with NewID()

  • Hi,

    I need to randomly select questions my database. i used the following query:

    SelectCommand="SELECT Top 10 MIN[QuestionID], [Title], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [QuestionOrder] FROM [Question] WHERE ([QuizID] = @QuizID) GROUP BY Title, ORDER BY NewID()"

    I tried and it keeps saying invalid syntax near Order.

  • shawndidy (2/21/2010)


    Hi,

    I need to randomly select questions my database. i used the following query:

    SelectCommand="SELECT Top 10 MIN[QuestionID], [Title], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [QuestionOrder] FROM [Question] WHERE ([QuizID] = @QuizID) GROUP BY Title, ORDER BY NewID()"

    I tried and it keeps saying invalid syntax near Order.

    Drop the comma between Title and ORDER BY.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • SELECT TOP (10)

    MIN[QuestionID],

    [Title],

    [Answer1],

    [Answer2],

    [Answer3],

    [Answer4],

    [CorrectAnswer],

    [QuestionOrder]

    FROM [Question]

    WHERE [QuizID] = @QuizID)

    GROUP BY Title

    ORDER BY NEWID();

    This can be inefficient.

    An alternative approach is to query the range of available QuestionIDs, pick a number in that range at random, and then SELECT out that one row.

    Paul

  • Tanks for your reply. I droped the "," and i got a syntax error

  • Hi SSCrazy,

    i have a total of 50 questions for a particular test. so i tied:

    SELECT Top(40) [QuestionID], [Title], [Answer1], [Answer2], [Answer3], [Answer4], [CorrectAnswer], [QuestionOrder] FROM [Question] WHERE ([QuizID] = @QuizID) GROUP BY Title ORDER BY NEWID();

    and i got the following error:

    Column 'Question.QuestionID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Column 'Question.QuestionID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Source Error:

    Line 14: protected void Page_Load(object sender, EventArgs e)

    Line 15: {

    Line 16: questionDetails.DataBind();

    Line 17: }

    Line 18:

    Source File: c:\Users\ShawnDidy\Desktop\QuizCS\questions.aspx.cs Line: 16

  • -- Sample table

    DECLARE @Question

    TABLE (

    question_id INTEGER IDENTITY PRIMARY KEY,

    quiz_id INTEGER NOT NULL,

    title NVARCHAR(50) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

    answer_1 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

    answer_2 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

    answer_3 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

    answer_4 NVARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

    correct_answer TINYINT NOT NULL CHECK (correct_answer BETWEEN 1 AND 4)

    );

    -- Add 50,000 rows of randon test data

    WITH

    -- Number generator

    L0 AS (SELECT 1 AS C UNION ALL SELECT 1),

    L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS (SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B),

    Numbers

    AS

    (

    SELECT TOP (50000)

    ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N

    FROM L4

    ORDER BY N

    )

    -- Random data generator

    INSERT @Question

    (quiz_id, title, answer_1, answer_2, answer_3, answer_4, correct_answer)

    SELECT ABS(CHECKSUM(NEWID())) % 10 + 1,

    N'title #' + CONVERT(NVARCHAR(2), ABS(CHECKSUM(NEWID())) % 99 + 1),

    CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

    CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

    CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

    CONVERT(NVARCHAR(3), ABS(CHECKSUM(NEWID())) % 999 + 1),

    ABS(CHECKSUM(NEWID())) % 4 + 1

    FROM Numbers

    -- This is the quiz_id we want questions for

    DECLARE @QuizID INTEGER;

    SET @QuizID = 5;

    -- 40 random questions

    SELECT TOP (40)

    Q.title,

    Q.answer_1,

    Q.answer_2,

    Q.answer_3,

    Q.answer_4,

    Q.correct_answer

    FROM @Question Q

    WHERE Q.quiz_id = @QuizID

    ORDER BY

    NEWID()

    OPTION (RECOMPILE);

  • Hi,

    Tanks alot for going to great length and writing this PL/SQL script. I'm still mastering Pl?QL.

    You might have guessed i'm using Visual Studio 2008 and my Database resides on MS Server 2008. Does it support this PL/SQL scripts coz when i created a Dataset to use the entire script as a SELECT Statement stored procedure, it returned d folowing error message saying:

    There were errors relating to data access code generation.

    Details:

    Generated Select statement.

    the "Declare" SQL Construct or Statement is not supported.

    Any suggestions on how to work around this

  • PL/SQL is Oracle.

    SQL Server's batch language is T-SQL.

    😛

  • Hi,

    lol...i know PL/SQL is the SQL dialect for oracle database and T-SQL is for microsoft server. I'm still a fresher! Can u tell me where to place the script you gave me, i tried running it in the microsoft visual studio, i created a dataset then under the SELECT, i entered the entire script...but i'm getting the error which says declare is unrecognised.

    Please help

  • shawndidy (2/25/2010)


    Tanks alot for going to great length and writing this PL/SQL script. I'm still mastering Pl?QL.

    This was the statement that led me to believe you didn't know 😉

    I have no idea how you should write the query in Visual Studio I am afraid. Maybe someone else can help.

  • Hi SS,

    You've being of immense help...i'll fix dat area...and let you know as sonn as i do. Once again tank u very much!

  • You're welcome.

  • Place the SQL Code in SP give input parameters if any....use SqlCommand, SqlConnection, Sql PArameters to exec SP and to pass Parameters in Visual Studio.....

  • hi,

    I created a Procedure and and placed the statements in the procedure....then i called it as a datasource and it worked. But its not yet working for my purpose yet.

    the expressions @Question, @Title, @Answer1, @Answer2, @Answer3, @Answer4, @CorrectAnswer all retrieve random numbers into the columns instead of questions. i need them to randomly pick the questions from my Questions Table in my database. Like so:

    Question = @Question,

    Title = @Title,

    Answer1 = @Answer1,

    Answer2 = @Answer2,

    Answer3 = @Answer3,

    Answer4 = @Answer4,

    CorrectAnswer = @CorrectAnswer

    Is there a way around this so that the select statement: retrieves questions, coz i think the number generator might be responsible. Any help please

  • It would probably help folks help you a lot if you could post some table info and some readily consumable data. See the following link for an article on the best way to do that. It does take a bit of time on your part (not much, though) and it will really help people understand your problem well enough to (usually) provide you with a tested coded answer.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 35 total)

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