Professional Web Applications Themes

Selecting 1 random row from each of n random groups - Microsoft SQL / MS SQL Server

I am constructing an exam. There are pools of questions, some number of questions per pool. I draw first question, at random, from pool #1, second question at random from pool #2, etc. Based on other posts, I've created the following query: SELECT TOP 100 PERCENT NUMORDER,QUESID,QUESTION FROM POOL,QUESTION POOL.POOLID=QUESTION.POOLID ORDER BY NUMORDER,NEWID() This displays the list of all questions in all pools, grouped by pool id(NUMORDER), and question order is randomized within each pool grouping. Since I only need the first question from each pool, how do I obtain this from this result set? (Or am I on the ...

  1. #1

    Default Selecting 1 random row from each of n random groups

    I am constructing an exam. There are pools of questions, some number
    of questions per pool. I draw first question, at random, from pool
    #1, second question at random from pool #2, etc. Based on other posts,
    I've created the following query:

    SELECT TOP 100 PERCENT NUMORDER,QUESID,QUESTION
    FROM POOL,QUESTION
    POOL.POOLID=QUESTION.POOLID
    ORDER BY NUMORDER,NEWID()

    This displays the list of all questions in all pools, grouped by pool
    id(NUMORDER), and question order is randomized within each pool
    grouping. Since I only need the first question from each pool, how do
    I obtain this from this result set? (Or am I on the wrong path)

    I've learned from other posts I can't use rowcount, and for this
    project I can't use any stored proc soln's (long story) so those posts
    couldn't be used either.

    Thanks in advance.
    Carl Guest

  2. #2

    Default Re: Selecting 1 random row from each of n random groups

    What about this design, since you did not post any DDL?

    CREATE TABLE QuestionPools
    (pool_nbr INTEGER NOT NULL,
    question_nbr INTEGER NOT NULL,
    question_txt VARCHAR(500) NOT NULL,
    PRIMARY KEY (pool_nbr, question_nbr));

    CREATE TABLE Drawing
    (pool_nbr INTEGER NOT NULL PRIMARY KEY,
    pick_nbr INTEGER NOT NULL);

    Stuff "Drawing" with all the pool numbers and then a random integer from
    zero to something big. The pick number is set to the range of question
    numbers in each pool with a MOD function.

    SELECT Q1. *
    FROM QuestionPools AS Q1, Drawings AS D1
    WHERE D1.pool_nbr = Q1.pool_nbr
    AND D1.question_nbr = Q1.question_nbr;

    At this point I am going to get lazy and do a "cut & paste" on random
    numbering:

    ===============
    Let's assume you have a function called RANDOM() that returns a random
    number between 0.00 and 1.00. If you just want one random row out of
    the table, and you have a numeric key column, Tom Moreau proposed that
    you could find the MAX() and MIN(), then calculate a random number
    between them.

    SELECT L1.*
    FROM LotteryDrawing AS L1
    WHERE col_1
    =(SELECT MIN(keycol)
    +(MAX(keycol) - MIN(keycol) * RANDOM()))
    FROM LotteryDrawing AS L1);

    Here is a version which uses the COUNT(*) functions and a self-join
    instead.

    SELECT L1.*
    FROM LotteryDrawing AS L1
    WHERE CEILING((SELECT COUNT(*) FROM LotteryDrawing)
    * RANDOM())
    =(SELECT COUNT(*)
    FROM LotteryDrawing AS L2
    WHERE L1.keycol <= L2.keycol);

    The rounding away from zero is important, since we are in effect
    numbering the rows from one. The idea is to use the decimal fraction
    to hit the row that is far into the table when the rows are ordered by
    the key.

    Having shown you this code, I have to warn you that the pure SQL has a
    ;od number of self-joins, and they will be expensive to run.

    Even though they can't contain subqueries, default constraints can be
    quite complex. Here's an example that defines a default constraint that
    supplies a random number default value:

    CREATE TABLE RandomNbrs
    (k1 INTEGER PRIMARY KEY,
    c1 FLOAT DEFAULT(
    (CASE(CAST(RAND()+.5 AS INTEGER)*-1) WHEN 0 THEN 1 ELSE -1
    END)*(CAST(RAND() * 100000 AS INTEGER) % 10000)*RAND()))

    INSERT INTO RandomNbrs VALUES(1, DEFAULT);
    INSERT INTO RandomNbrs VALUES(2, DEFAULT);
    INSERT INTO RandomNbrs VALUES(3, DEFAULT);
    INSERT INTO RandomNbrs VALUES(4, DEFAULT);
    INSERT INTO RandomNbrs VALUES(5, DEFAULT);
    ...
    INSERT INTO RandomNbrs VALUES(10, DEFAULT);

    SELECT * FROM RandomNbrs

    k1 c1
    ----------- -----------------------------------------------------
    1 -121.89758452446999
    2 -425.61113508053933
    3 3918.1554683876675
    4 9335.2668286173412
    5 54.463890640027664
    6 -5.0169085346410522
    7 -5430.63417246276
    8 915.9835973796487
    9 28.109161998753301
    10 741.79452047043048

    The(CASE(CAST(RAND()+.5 AS INTEGER)*-1) WHEN 0 THEN 1 ELSE -1 END)
    expression randomizes the sign of the generated number, allowing for
    both positive and negative numbers, while the(CAST(RAND() * 100000 AS
    INTEGER) % 10000)*RAND() expression generates an INTEGER between 0 and
    9999.

    Carl Federl had a trick for getting a non-deterministic function in SQL
    Server. The workaround for non-deterministic functions is to first
    create a view, then create a UDF that selects from the view and finally,
    reference the UDF in the query.

    CREATE VIEW Rand_Nbrs(rand_nbr)AS SELECT RAND();

    CREATE FUNCTION dbo.Get_Rand_Nbr( ) RETURNS REAL
    AS
    BEGIN
    DECLARE r REAL;
    SET r =(SELECT rand_nbr FROM rand_nbrs);
    RETURN r;
    END;

    to use it:

    SELECT (SELECT CAST(Get_Rand_Nbr() * 100 AS INTEGER) AS rand_nbr, ..
    FROM Foobar
    WHERE ...;

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Joe Guest

  3. #3

    Default Re: Selecting 1 random row from each of n random groups

    Hello!

    You can try the following:

    CREATE TABLE POOL (POOLID INT NOT NULL PRIMARY KEY)
    go
    CREATE TABLE QUESTION (QUESTIONID INT NOT NULL PRIMARY KEY,
    POOLID INT NOT NULL FOREIGN KEY REFERENCES POOL,
    QUESTION VARCHAR(2000) NOT NULL)
    go
    INSERT INTO POOL
    SELECT 1
    UNION
    SELECT 2

    INSERT INTO QUESTION (QUESTIONID, POOLID, QUESTION)
    SELECT 1, 1, '1.1'
    UNION
    SELECT 2, 1, '1.2'
    UNION
    SELECT 3, 2, '2.1'
    UNION
    SELECT 4, 2, '2.2'
    UNION
    SELECT 5, 2, '2.3'
    UNION
    SELECT 6, 1, '1.3'

    SELECT q.QUESTIONID, q.POOLID
    FROM QUESTION q
    WHERE q.QUESTIONID = (SELECT TOP 1 q1.QUESTIONID
    FROM QUESTION q1
    WHERE q1.POOLID = q.POOLID
    ORDER BY RAND((SIN(DATEPART(ms,GETDATE())*q1.QUESTIONID*100 00)+1)*300))
    ORDER BY 2, 1

    DROP TABLE QUESTION
    DROP TABLE POOL


    com (Carl) wrote in message news:<google.com>... 
    Pavel Guest

Similar Threads

  1. Selecting objects within groups
    By Jonathan_Carr@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: October 8th, 07:48 PM
  2. Selecting users that are in multiple groups
    By jlavetan in forum Macromedia ColdFusion
    Replies: 10
    Last Post: June 13th, 05:44 PM
  3. Using Math.random to go to random frames
    By Glen Gummess webforumsuser@macromedia.com in forum Adobe Flash, Flex & Director
    Replies: 2
    Last Post: January 23rd, 10:26 PM
  4. selecting ordered groups out of a table
    By Ian D Bjorhovde in forum IBM DB2
    Replies: 0
    Last Post: August 30th, 01:57 PM
  5. Random image in a random place.
    By JoshR webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 8th, 03:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139