Originally Posted by plog
I would do this with a subquery:
1. If you don't need the ID field in the final results then 'sub1' will do what you need in the most effecient manner.
2. Arnelgp's solution will be the most efficient provided you never have a gap in versions. If OPID=1 has 2 records with a gap in versions (Version=1 & Version=5) it will not return accurate results.
3. An index on OPID and Version would help in all cases. Also, if Version only contains numeric data, it should be of a numeric type. A Varchar or other text field for this will cause speed issues.
Great tips and advise. Thanks a lot for your help..
In regards to item #1 of your comments, can i interpret your words as if I do not need the Primary Key (ID) in my data, there is absolutely no reason to
use the JOIN query? I do not need the primary key (ID). Just associated data with the max version. So, with other fields i need data for I was able to get everything i need with:
SELECT External2.OpID, External2.Type, External2.State, External2.Kind, External2.Number, Max(External2.version) AS LastVersion
GROUP BY External2.OpID, External2.Type, External2.State, External2.Kind, External2.Number;
And this is MUCH faster than the original method.
Thanks best wishes.