selecting a certain number of records 2

[memphisTNdev22]

How can I select a different number of records for different data within a column..For example...i need to select 50 records where type = 'A', 100 records where type = 'B', and 25 records where type = 'C'

 

[SantaMufasa]

Memphis, you can use this style of code:

SELECT <expression_list>
FROM (SELECT * FROM <source_table> WHERE type = 'A')
WHERE ROWNUM <= 50
UNION
SELECT <expression_list>
FROM (SELECT * FROM <source_table> WHERE type = 'B')
WHERE ROWNUM <= 100
UNION
SELECT <expression_list>
FROM (SELECT * FROM <source_table> WHERE type = 'C')
WHERE ROWNUM <= 25
ORDER BY <whatever order you want for the results>;

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[memphisTNdev22]

great that worked! How would I retrieve 27 percent of records = 'A', 36 percent of records = 'B', and 36 percent of records = 'C' from one table

 

[SantaMufasa]

Then try this code adjustment:

SELECT <expression_list>
FROM (SELECT * FROM <source_table> WHERE type = 'A')
WHERE ROWNUM <= (select .27*count(*) from <source_table> WHERE type = 'A')
UNION
SELECT <expression_list>
FROM (SELECT * FROM <source_table> WHERE type = 'B')
WHERE ROWNUM <= (select .36*count(*) from <source_table> WHERE type = 'B')
UNION
SELECT <expression_list>
FROM (SELECT * FROM <source_table> WHERE type = 'C')
WHERE ROWNUM <= (select .36*count(*) from <source_table> WHERE type = 'C')
ORDER BY <whatever order you want for the results>;

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cranebill]

SELECT <expression_list>
FROM <source_table> sample(27) WHERE type = 'A'
UNION
SELECT <expression_list>
FROM <source_table> sample(36)WHERE type = 'B'
UNION
SELECT <expression_list>
FROM <source_table> sample(36) WHERE type = 'C';

 

[SantaMufasa]

Good call, Bill. Hava Purple Star !

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cranebill]

Thank you