help with SQL Query and MAX

[karmst]

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

[jmodic]

select * from submittal_line_item
where (submittal_id, version_nbr) in
(select submittal_id, max(version_nbr) from submittal group by submittal_id);