I have below single table , in which we have same 3 rocords with differnt version, We need to fetch records with their highest version.
table
Id name value verion
1 raj java 8
2 raj stack 4
3 prem abc 6
4 raj google 1
5 prem pqr 11
and many more like this
Output should look like this
Id name value verion
1 raj java 8
5 prem pqr 11
I have already tried to find the solution with below Stack over question, but I am using single table, not able to find the solution
Selecting most recent and specific version in each group of records, for multiple groups
CodePudding user response：
Using subquery we can find max verion per name.
select m.*
from my_table m
inner join (select name,
max(verion) as mx_ver
from my_table
group by name
) as mx_ on mx_.name=m.name and mx_.mx_ver=m.verion;