cancel
Showing results for 
Search instead for 
Did you mean: 

Record Selection To Include Only Last Record of LOJ B table

Former Member
0 Kudos

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...

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this query

select * from CALL inner join CALLNOTE on CALLNOTE.CALL_ID=CALL.CALL_ID where CALLNOTE.CALN_DATE in (select max(CALN_DATE) from CALLNOTE group by CALL_ID)

Regards,

Raghavendra.G

Answers (1)

Answers (1)

Former Member
0 Kudos

Thanks!

Fuskie

Who loves data environments where his only access to raw data is through Crystal...