Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SQL Random selection with NewID() Expand / Collapse
Author
Message
Posted Sunday, February 21, 2010 3:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 11:58 AM
Points: 15, 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.

Post #869804
Posted Sunday, February 21, 2010 3:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 1,074, Visits: 6,383
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
Post #869807
Posted Sunday, February 21, 2010 3:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #869815
Posted Wednesday, February 24, 2010 8:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 11:58 AM
Points: 15, Visits: 29
Tanks for your reply. I droped the "," and i got a syntax error
Post #872445
Posted Wednesday, February 24, 2010 8:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 11:58 AM
Points: 15, 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
Post #872448
Posted Wednesday, February 24, 2010 9:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
-- 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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #872469
Posted Thursday, February 25, 2010 7:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 11:58 AM
Points: 15, 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
Post #873140
Posted Friday, February 26, 2010 9:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
PL/SQL is Oracle.
SQL Server's batch language is T-SQL.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #873854
Posted Saturday, February 27, 2010 5:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 11:58 AM
Points: 15, 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
Post #873964
Posted Saturday, February 27, 2010 5:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 11,194, Visits: 11,112
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #873967
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse