on 06-11-2009 8:44 PM
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...
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks!
Fuskie
Who loves data environments where his only access to raw data is through Crystal...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.