I have a table in which multiple versions of some records are stored. I'm designing a report that will list the most recent version of each record. The SQL I'm trying to duplicate is:
Select * from TableA A where sequence=(select max(sequence) from TableA B where A.partialkey = B.partialkey)

The SQL works, but I can't seem to translate it into Crystal formulas. It looks like a subreport should work (linking with partialkey, returning max(sequence) in a shared variable, and suppressing details when sequence < the variable) but it's inconsistent and I can't figure out why.

Does anyone have any suggestions? Thx.

Afraid I couldn't find the post you were referring to, but I've noticed something that might help. I added grouping by partialkey & displayed all the sequence numbers (subreport formula value, main report formula value, and record value) and noticed that it wasn't reporting a main report formula value for the 1st record & that all subsequent records reported the previous value-and it didn't report all the records but I need to modify my test data to check why. I think it isn't resetting the main report formula value & therefore ended at the partialkey record with the highest sequence-which happened to also be the highest partialkey that got reported.

If that's the case I've got two issues: evaluation time & resetting.

Yes, that looks like an appropriate post-don't know why I didn't see it, but I arrived at the same conclusion while testing the anomalies in the evalution timing. Put the subreport in the group header & all the inconsistencies went away. Marvelous. Thanks, all.