CODEWITHSUNDEEP
sqlteradata
user3654225
user3654225

Reputation: 109

pulling data from max field

I have a table structure with columns similar to the following:

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

ideally, i'd like to pull the following:

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

one solution would be to do something like the following:

select a.ID, a.value
from
myTable a
inner join (select id, max(line) as line from myTable group by id) b
    on a.id = b.id and a.line = b.line

Given the size of the table and that this is just a part of a larger pull, I'd like to see if there's a more elegant / simpler way of pulling this directly.

Upvotes: 0

Views: 38

Answers (1)

dnoeth
dnoeth

Reputation: 60462

This is a task for OLAP-functions:

select *
from myTable a
qualify
   rank()  -- assign a rank for each id
   over (partition by id
         order by line desc) = 1

Might return multiple rows per id if they share the same max line. If you want to return only one of them, add another column to the order by to make it unique or switch to row_number to get an indeterminate row.

Upvotes: 2

Related Questions