Little more tricky & complex randomness functionality

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Little more tricky & complex randomness functionality

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-14 : 09:26:28

Navinderjit writes "I had gone through the 'randomness' topic in Queries section , But my problem is little more tricky.
I am having one ASP-SQL 7.0 Application.
I am having one 'Question' table, with 3 fields.
-'id' as the the identity column
-'question_text' as the varchar datatype
-'level' which is having int datatype and having values serially between 1 to 10.

There may be more than one question with different 'id' having same 'level'
value.

Now I want to pick say total of 10 questions from 'Question' table, in
order of there 'level', but it should pick random questions from the
'question' table having the same 'level' value. For eg. the first question would
be from any of the question having there 'level' as '1', and second question should
be from questions having 'level' as '2', and similar for the next 8
questions.

Thanks in advance

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-14 : 11:14:38

Something like this atrocity would work too*:


SELECT * FROM Question
WHERE id IN (
    SELECT CAST(SUBSTRING(MAX(CAST(NEWID() AS char(36)) +
           CAST(id AS varchar(19))),37,19) AS int)
    FROM Question
    GROUP BY Level)

* Hmm, Rob seems to have deleted his idea.

It's probably worth checking the performance of this versus doing it with a join instead of a subquery, especially if you have 10 levels and 1000's of questions.

Edited by - Arnold Fribble on 03/14/2002 11:48:20