How do i get records from table in pl/sql?

Question

Hi!!!

I have one table : Questionmaster. it stores DisciplineId,QuestionId,QuestionText etc...

Now My Question is:

I need 10 records of particular DisciplineId, 20 records for another DisciplineId and 30 records for Someother DisciplineId.... What should I do for that? How can I club all statement and get just 60(10+20+30) rows selected?

For one Discipline,it is working as shown below:

create or replace function fun_trial(Discipline1,Disc1_NoOfQuestions)
 open cur_out for 
  select getguid() tmp,
  QuestionNo,QuestionText,
  Option1,Option2,
  Option3,Option4,
  Correctanswer,Disciplineid
  from  Questionmaster
  where DisciplineId=discipline1
  AND  rownum <= disc1_NoOfQuestions
   order by tmp ;
return (cur_out);

Answer 1

The following query uses the analytic function RANK() to sort the questions within discipline. The outer query then selects the first ten, first twenty and first thirty questions for disciplines 1, 2 and 3 respectively.

select * from (
  select getguid() tmp
         , QuestionNo
         , QuestionText
         , Option1
         , Option2
         , Option3
         , Option4
         , Correctanswer
         , Disciplineid
         , rank () over (partition by Disciplineid order by QuestionNo ) as rn 
  from  Questionmaster
  where DisciplineId in (1, 2, 3)
)
where ( DisciplineId = 1 and rn <= 10 )
or    ( DisciplineId = 2 and rn <= 20 )
or    ( DisciplineId = 3 and rn <= 30 )
/