select records randomly

[deepak_8917]

i have a database containing 100 questions . i want to select random 10 from it. how to do that?

there is another database containing 100 questions. questions are stored along with an attribute level.
there are 10 levels. so there are 10 records whose level value is 1
and 10 records whose level value is 2 and so on.. upto 10levels.
now i want to select random 20 questions from those 100, choosing 2
from each level

can any one please tell what are the sql queries for both the questions?

[anubina]

Solution for >=Ora10g (only query):

SELECT q_level,
          r_number_1,
          r_number_2
     FROM (SELECT 1 AS lev FROM dual UNION
           SELECT 2 AS lev FROM dual UNION
           SELECT 3 AS lev FROM dual UNION
           SELECT 4 AS lev FROM dual UNION
           SELECT 5 AS lev FROM dual UNION
           SELECT 6 AS lev FROM dual UNION
           SELECT 7 AS lev FROM dual UNION
           SELECT 8 AS lev FROM dual UNION
           SELECT 9 AS lev FROM dual) p
MODEL PARTITION BY (lev) DIMENSION BY (0 AS i)
MEASURES (lev AS q_level,CAST(NULL AS NUMBER(1,0)) AS r_number_1,CAST(NULL AS NUMBER(1,0)) AS r_number_2)
RULES UPSERT ITERATE (100) UNTIL (r_number_1[0]!=r_number_2[0]) (
 r_number_1[0]=round(dbms_random.value(1,9))
,r_number_2[0]=round(dbms_random.value(1,9))
,r_number_2[0]=CASE WHEN r_number_1[0] != r_number_2[0] THEN r_number_2[0] ELSE r_number_1[0] END
)
ORDER BY q_level

For 9i and lowest you need use PL.

PS. The view-inline is the table of your questions, you can sustitute:

SELECT 1 AS lev FROM dual UNION
           SELECT 2 AS lev FROM dual UNION
           SELECT 3 AS lev FROM dual UNION
           SELECT 4 AS lev FROM dual UNION
           SELECT 5 AS lev FROM dual UNION
           SELECT 6 AS lev FROM dual UNION
           SELECT 7 AS lev FROM dual UNION
           SELECT 8 AS lev FROM dual UNION
           SELECT 9 AS lev FROM dual

for SELECT question_level FROM t_questions Sorry but my level english is very poor