Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2016
    Posts
    15

    Lightbulb Unanswered: How to make one Issue to show max of only three solutions

    Hi Experts,

    Am new to database concepts and trying to learn on own with few ebooks. I have created two tables one table as Issues (It has issue id as PK and title as columns) and another table as Solutions (It has solution id as PK, solution, issue id as FK columns).
    Tables are created with columns but now my question is:
    Each issue will have more than one solution but I want to display only top three solutions for each issue. So how can I achieve this?
    Any help is really appreciated.

    Please let me know if You need any additional inputs.

    Thanks,
    Richa

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Top 3 solutions for each issue" - measured by what? I'm not sure whether you can do that, based on data model you currently have (or - alternatively - I don't understand what you are saying).

    It would help if you could post an example which illustrates what you meant (i.e. contents of those two tables and desired output).

  3. #3
    Join Date
    Feb 2016
    Posts
    15
    Hi,
    Thanks for the reply and sorry if question is confusing. Am attaching the logical and ER Diagram for reference:
    Structure:
    Click image for larger version. 

Name:	Structure.jpg 
Views:	5 
Size:	84.2 KB 
ID:	16790

    ER Diagram:
    Click image for larger version. 

Name:	ERD.JPG 
Views:	5 
Size:	35.9 KB 
ID:	16791

    Example:
    Click image for larger version. 

Name:	Example.JPG 
Views:	7 
Size:	36.0 KB 
ID:	16792

    Please correct me if am wrong in any of the approach. Am learning things on own and have difficulty in start.

    Regards,
    Richa

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Based on sample data you provided in the third image, it appears that "top 3" is based on the first character so the following code might do the job:
    Code:
    ... where substr(recmn, 1, 1) <= 3
    If there are recommendations that have two-digit numbers (such as 10, 11, ...), then this would be a better idea:
    Code:
    ... where to_number(regexp_substr(col, '^\d+')) <= 3
    However, I think that - if you chose to use such a model - you'd better split "recommendation" string into two columns: one which would contain the ranking number (1, 2, 3, ...), and another one which contains description. In that case, you'd simply
    Code:
    ... where recmn_number <= 3
    i.e. no functions are required.

  5. #5
    Join Date
    Feb 2016
    Posts
    15
    Wow that's great help and Thank You so much. I feel the last method will be better for me as its simple and works great. Thanks again. I was waiting to get reply for a long time and You saved my day.

Posting Permissions

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