SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Random selection with NewID()


SQL Random selection with NewID()

Author
Message
shawndidy
shawndidy
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 29
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.
ChrisM@home
ChrisM@home
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9604 Visits: 10725
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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60884 Visits: 11396

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
SQLblog.com
@SQL_Kiwi
shawndidy
shawndidy
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 29
Tanks for your reply. I droped the "," and i got a syntax error
shawndidy
shawndidy
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 29
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
Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60884 Visits: 11396

-- 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
SQLblog.com
@SQL_Kiwi
shawndidy
shawndidy
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 29
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
Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60884 Visits: 11396
PL/SQL is Oracle.
SQL Server's batch language is T-SQL.
:-P



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
shawndidy
shawndidy
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 29
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
Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60884 Visits: 11396
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search