Skip to Content
0
Jun 11, 2009 at 07:44 PM

Record Selection To Include Only Last Record of LOJ B table

23 Views

Here is the setup. I have two tables, CALL and CALLNOTE:

CALL
====
CALL_ID
CALL_DATE

CALLNOTE
=========
CALL_ID
CALN_ID
CALN_DATE
CALN_NOTE

CALL *= CALLNOTE

I need to return one row of data for each CALL_ID, with CALL_ID and CALL_DATE coming from the CALL table, and CALN_ID, CALLN_DATE and CALLN_NOTE coming from the sequentially last CALN_ID from CALLNOTE associated with the CALL table (left outer link on CALL_ID). For technical reasons I will not go into, I cannot just group by CALL_ID and use the last record in the group footer - I need to filter the data coming into the report either through record selection or through a SQL command, probably the latter but I cannot figure out how to configure a SELECT query to return only the last record in a group.

My end result should be the following:

CALL_ID, CALL_DATE, CALLN_ID, CALN_DATE, CALN_NOTE

One row per call_ID.

Fuskie

Who appreciates any help y'all can provide...