February 21, 2010 at 3:13 am
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.
February 21, 2010 at 3:18 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 21, 2010 at 3:49 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2010 at 8:52 pm
Tanks for your reply. I droped the "," and i got a syntax error
February 24, 2010 at 8:58 pm
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
February 24, 2010 at 9:57 pm
-- 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);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 25, 2010 at 7:37 pm
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
February 26, 2010 at 9:54 pm
PL/SQL is Oracle.
SQL Server's batch language is T-SQL.
😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2010 at 5:22 am
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
February 27, 2010 at 5:26 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 28, 2010 at 8:27 pm
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!
February 28, 2010 at 10:21 pm
You're welcome.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 5, 2010 at 8:42 am
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.....
March 7, 2010 at 2:16 am
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
March 7, 2010 at 9:40 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply