How to select records which have the highest version without getting the old versions record?

Question

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

Get max version per name in aggregating subquery. Get according rows in outer query. — Akina, Dec 29 '22 at 08:58

score 1 · Accepted Answer · answered Dec 29 '22 at 09:02

1

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;

https://dbfiddle.uk/NavF5X_K

answered Dec 29 '22 at 09:02

Ergest Basha

7,870
4
8
28

let me try this, will confirm result here – Tushar Dec 29 '22 at 09:04
1

Thank you so much for the most accurate solution. @Ergest Basha – Tushar Dec 29 '22 at 10:19

How to select records which have the highest version without getting the old versions record?

1 Answers1