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?

2
Contributors
1
2
Views
9 Years
Discussion Span
Last Post by 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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.