-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|