SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting records from different categories - oracle

    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:
    Code:
    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:
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you really do need to indicate what you've attempted

    we don't actually do homework assignments for free on this forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried the below query and its not satisfying for scenario1.

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

  4. #4
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found the solution. Posting the query here so that it will be helpful for others.

    Code:
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vpalukuru9 View Post
    Posting the query here so that it will be helpful for others.
    thank you for this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •