View Single Post
Old 09-22-2019, 01:27 PM   #5
Newly Registered User
Join Date: Sep 2019
Posts: 30
Thanks: 4
Thanked 1 Time in 1 Post
ironfelix717 is on a distinguished road
Re: Sort by MAX Version query - Very slow

Originally Posted by plog View Post
I would do this with a subquery:

3 notes:

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
FROM External2
GROUP BY External2.OpID, External2.Type, External2.State, External2.Kind, External2.Number;
And this is MUCH faster than the original method.

Thanks best wishes.
ironfelix717 is offline   Reply With Quote