Member Avatar for 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?

  • 2 Contributors
  • 1 Reply
  • 223 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by anubina

Dani AI

Generated

Good point from to think in terms of generating random numbers per group, but the same goals are usually simpler with standard SQL. For a small-to-moderate table, ordering by a random value is straightforward; for "two per level" use an analytic function to pick the top N per partition.

Simple — pick 10 random rows:

SELECT *
FROM (
  SELECT *
  FROM t_questions
  ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 10;

Two random rows from each level (replace question_level and t_questions with the actual column/table names):

SELECT *
FROM (
  SELECT q.*,
         ROW_NUMBER() OVER (PARTITION BY question_level ORDER BY DBMS_RANDOM.VALUE) rn
  FROM t_questions q
) t
WHERE rn <= 2;

Notes and troubleshooting:

  • ORDER BY DBMS_RANDOM.VALUE must be inside a subquery if ROWNUM is used to limit results; otherwise ROWNUM applies before ordering.
  • If any level has fewer than two rows, the second query will return only the available rows for that level.
  • For reproducible results, seed the generator before running queries (for example, DBMS_RANDOM.SEED(<number>) in a PL/SQL block).
  • Performance: both approaches sort data; on very large tables this can be expensive. For heavy workloads consider: a periodically maintained random_val column indexed and refreshed, sampling by ROWID, or a PL/SQL routine that picks rows without a full sort.
  • If running a very old Oracle release that lacks analytic functions, a small PL/SQL loop or temporary-table approach will be necessary (as noted by ).
Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.