Find Max date item in subgroup of data

[DrSmyth]

Hi,

My query relates to two tables, PSC_ESTA is the primary table that LIA_DIRY, which is a diary table, is attached to.
They are connected by a one to may relationship by a field called ESTA_COD.

I want to report on a particular diary event (EVNT_COD) with a particular narrative (DIRY_NAR), and I want to find the last instance of this event with this narrative.

I've tried creating an alias of the LIA_DIRY table and applying a complex join based on the DIRY_EVNT and DIRY_NAR that i want to look at, this works fine for simply bringing back the dataset that i want to look at.

The problem occurs when i try and add a calculation condition to this dataset in the report. I want just the last occurance of my dataset in each PSC_ESTA, but I'm only getting where my dataset is the last occurance overal (including anything that has been eliminated by the join). This has severely restricted my return (i'm expecting about 3,000 rows but am getting only 35).

Don't think I've explained this particularly well (sorry) but i can answer any questions that might clarify....

Can anybody help me??

Thanks

 

[blom0344]

Dr Smyth,

Post the SQL statement that you currently have and I'll have a look. Viewing the SQL will give me enough of a clue I expect..

Ties Blom
Information analyst

 

[sh4kil]

You have to have a sub query that returns the Max Date per DIRY_EVNT and DIRY_NAR rather than the Max Date overall.

1 - You should be able to put this in the complex join,

or

2 - How about a view or derived table that you can use as a bridging table,

e.g.

View would be something like ESTA_COD, DIRY_EVNT, DIRY_NAR, MAX(Date Column)


I would persevere with Option 1.