How to get desired type of records for desired number of times in oracle I want to get get 6 "double_scoring" records and 4 "singe_scoring" records from my Profiles table based on SCORING_RULE column values. Can you please let me know how can I do this with SELECT query where I am just selecting 10 records with ROWNUM &lt;= 10 limit.
Please try this query:

select * from PROFILES where 
SCORING_RULE = 'double_scoring' and
ROWNUM &lt;= 6
union
select * from PROFILES where 
SCORING_RULE = 'single_scoring' and
ROWNUM &lt;= 4


Check it here: http://sqlfiddle.com/#!4/7ecc95/1