SQL for getting data from the row with the highest version value

Question

I have a table with data like this:

Id     | Version | isLive
-------+---------+-------
comp1  |   2     | true     
comp1  |   3     | true     
comp1  |   4     | false    
comp1  |   1     | true      
comp2  |   4     | true      
comp2  |   1     | false

I want to fetch the row that has the highest version number per ID. So there should only be one row per ID.

select id 
from mytable 
group by id

This returns the data randomly.

Look at this https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Christian H, Feb 27 '19 at 19:09

score 0 · Answer 1 · answered Feb 27 '19 at 19:10

0

From here

SELECT * FROM (SELECT * FROM MyTbl ORDER BY version ) WHERE rownum = 1;

answered Feb 27 '19 at 19:10

Johann

349
2
9

score 0 · Answer 2 · answered Feb 27 '19 at 19:12

0

Is this what you want ?

         select id,max(distinct version) from 
         mytable 
        group by id order by id

answered Feb 27 '19 at 19:12

Himanshu

3,830
2
10
29

score 0 · Answer 3 · answered Feb 27 '19 at 19:29

How about this:

SQL> with test (id, version, islive) as
  2    (select 'comp1', 2, 'true'  from dual union all
  3     select 'comp1', 3, 'true'  from dual union all
  4     select 'comp1', 4, 'false' from dual union all
  5     select 'comp1', 1, 'true'  from dual union all
  6     select 'comp2', 4, 'true'  from dual union all
  7     select 'comp2', 1, 'false' from dual
  8    )
  9  select id, version, islive
 10  from (select id, version, islive,
 11          row_number() over (partition by id order by version desc) rn
 12        from test
 13       )
 14  where rn = 1;

ID       VERSION ISLIV
----- ---------- -----
comp1          4 false
comp2          4 true

SQL>

SQL for getting data from the row with the highest version value

3 Answers3