How to get desired type of records for desired number of times in oracle

oracle-10gselect

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 <= 10 limit.
enter image description here

Best Answer

Please try this query:

select * from PROFILES where 
SCORING_RULE = 'double_scoring' and
ROWNUM <= 6
union
select * from PROFILES where 
SCORING_RULE = 'single_scoring' and
ROWNUM <= 4

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

Related Solutions

Read Oracle BLOB type via SQL

This oracle package will do the job

select dbms_lob.substr(b,2000,1) from blobtest;

MySQL – How to Select Specific Rows if a Column Exists or All Rows if a Column Doesn’t

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT 
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true

Best Answer

Related Solutions

Read Oracle BLOB type via SQL

MySQL – How to Select Specific Rows if a Column Exists or All Rows if a Column Doesn’t

Related Question