sql - pulling data from max field -

i have table structure columns similar following:

id | line | value 1  |  1   | 10 1  |  2   | 5 2  |  1   | 6 3  |  1   | 7 3  |  2   | 4 

ideally, i'd pull following:

id | value 1 | 5 2 | 6 3 | 4 

one solution following:

select a.id, a.value mytable inner join (select id, max(line) line mytable group id) b     on a.id = b.id , a.line = b.line 

given size of table , part of larger pull, i'd see if there's more elegant / simpler way of pulling directly.

this task olap-functions:

select * mytable qualify    rank()  -- assign rank each id    on (partition id          order line desc) = 1 

might return multiple rows per id if share same max line. if want return 1 of them, add column order by make unique or switch row_number indeterminate row.