help with SQL Query and MAX
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: help with SQL Query and MAX

  1. #1
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    Hi all,

    I have a problem.

    I have a SUBMITTAL record, which has SUBMITTAL_ID and VERSION_NBR as part of the PK.

    As a child table, I have SUBMITTAL_LINE_ITEM, which is related to SUBMITTAL_ID and VERSION_NBR as FK. There is a 1-m relationship between these 2 tables.

    How do i write a query to bring back only the records that relate to the highest VERSION_NBR for a SUBMITTAL_ID in the SUBMITTAL table?

    eg:
    SUBMITTAL Table
    ***************
    SUMBITTAL_ID VERSION_NBR
    1 1
    1 2
    1 3
    2 1
    2 2

    SUBMITTAL_LINE_ITEM Table
    ************************
    SUBMITTAL_LINE_ITEM_ID SUMBITTAL_ID VERSION_NBR
    1 1 1
    2 1 1
    3 1 1
    4 1 2
    5 1 2
    6 1 2
    7 1 3
    8 2 1
    9 2 2

    In this example, I would return SUBMITTAL_LINE_ITEM 7 for SUBMITTAL_ID 1, VERSION_NBR 3 and SUBMITTAL_LINE_ITEM 9 for SUBMITTAL_ID 2, VERSION_NBR 2 - FORGETTING ABOUT THE REST

    THANKS IN ADVANCE.

    -ka

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    select * from submittal_line_item
    where (submittal_id, version_nbr) in
    (select submittal_id, max(version_nbr) from submittal group by submittal_id);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width