|Subject||Re: select query|
> Assuming you are using Fb 2.0 or higher, have you tried a derived table to bypass the strictures on your unique SUBJECT?it is necessary to add
> 1. Put a DESCENDING index on Message."TIMESTAMP"
> SELECT deriv.NAME, m.SUBJECT, deriv.MOST_RECENT
> FROM MESSAGE m
> max( M1."TIMESTAMP" )
> FROM MESSAGE M1
> JOIN PERSON P
> ON P.ID = M1.PERSON_ID
> GROUP BY 1, 2) as deriv ( PERSON_ID, NAME, MOST_RECENT)
> ON m.PERSON_ID = derv.PERSON_ID
> (or some perhaps more efficient variation)
where deriv.MOST_RECENT = m.MOST_RECENT
condition to the end of the select. After that it works as expected.
I was thinking about something like that, but I was not able to construct such a derived table.