Selecting records from different categories - oracle

Databases
1

I have a products table. The products are grouped into 3 categories. I have to write only sql query (not plsql) to select the data from this products table. The requirements are:

  • Select 9 products randomly such that 3 products should be selected from each category.
  • If a category has less than 3 products (let say 2), then select the remaining (1 product) number of products from other categories.

Source table data:

Scenario1:


product_name, category
----------------------------------
XMN21, A
XMP22, A

YMN21, B
YMP22, B
YMQ23, B
YMR24, B
YMS25, B

ZMN21, C
ZMP22, C
ZMQ23, C
ZMR24, C
ZMS25, C

As the category A has only 2 products, the output should contain these 2 products and the remaining 7 products should be selected randomly from other categories such that at least 3 products should be selected each of the categories B and C.

Scenario2:


product_name, category
----------------------------------
XMN21, A
XMP22, A
XMQ23, A
XMR24, A

YMN21, B
YMP22, B
YMQ23, B
YMR24, B
YMS25, B

ZMN21, C
ZMP22, C
ZMQ23, C
ZMR24, C
ZMS25, C

Here all the categories contain more than 3 products. So, the query should return 3 products randomly from each category.

Gurus, Please help me in solving this problem and let me know if you are not clear with this.

2

you really do need to indicate what you’ve attempted

we don’t actually do homework assignments for free on this forum

3

I tried the below query and its not satisfying for scenario1.


select  *
from 
(
select   product_name,
             category,
             row_number() over(partition by category) r
from     products
) p
where  r <= 3
4

I found the solution. Posting the query here so that it will be helpful for others.


select  *
from
(
select  product_name,
             category,
             row_number() over(Order by r) r1
from
(
select   product_name,
             category,
             row_number() over(partition by category) r
from     products
) p
where  r <= 3
) p1
where r1 <= 9
5

thank you for this :slight_smile: