Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Tokyo
    Posts
    10

    Red face Unanswered: Help required in writing a query

    hi

    Consider a table (ShogenValue) with following data:


    Model_Id header_id Serial_no shogen_no si_val
    4329 1 40001 A0001 1

    4329 1 40001 B0001 2
    4329 1 50300 B0001 3

    4329 1 40001 C0001 4
    4329 1 50300 C0001 5
    4329 1 60001 C0001 6


    Select
    si_val
    From
    ShogenValue
    Where
    model_id = 4329
    And
    header_id = 1
    And
    serial_no = '60001'

    Is it possible to write a modified version of above query which will bring the following result:

    Model_Id header_id Serial_no shogen_no si_val
    4329 1 40001 A0001 1
    4329 1 50300 B0001 3
    4329 1 60001 C0001 6

    Meaning if a record exists for a particular shogen_no (Shogen_no C0001 has value corrosponding to '60001' in above query), pick it up, if it

    does not exist (Shogen_no B0001 does not have value corrosponding to '60001' in above case) then pick a record with one level less serial

    number ('50300' in above case) and so on.

    Thanx
    Omer Imtiaz
    Omer Imtiaz

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help required in writing a query

    Use MAX to get the highest shogen_no for each Serial_no:

    select serial_no, max(shogen_no) max_shogen_no
    from shogen_value
    where ...
    group by serial_no;

    Then join that to main query, e.g

    select ...
    from shogen_value
    where (serial_no, shogen_no) in
    (
    select serial_no, max(shogen_no) max_shogen_no
    from shogen_value
    where ...
    group by serial_no
    );

Posting Permissions

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