Oracle: max value of each observation, and all rows associated with max value

Question

I have a table that looks something like so:

CustomerId  TransactionID  ReceiptLine  Value
100         200               A          .90
100         200               B          .95
100         201               A          1.06
101         199               A          1.00
101         200               B          .99  
101         200               ZZ         1.02
102         300               R          1.00
102         305               R          1.00
102         305               T          1.03
...

And I would like to get the max TransactionID for each CustomerId and all the columns to the right of the TransactionID associated with the TransactionID.

So my hypothetical query based on the specs I've described would return:

CustomerId  TransactionID  ReceiptLine  Value
100         201               A          1.06
101         200               B          .99  
101         200               ZZ         1.02
102         305               R          1.00
102         305               T          1.03

Any thoughts or help would be greatly appreciated. I've spent a good amount of time on this and at the moment I'm to the point of returning the max TransactionID of the entire set and its corresponding CustomerID, ReceiptLines, and Values, but that's all it's returning.

Thanks

Answer 1

Try the following query:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT CustomerID, TransactionID,
           ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Value DESC) rn
    FROM yourTable
) t2
    ON t1.CustomerId    = t2.CustomerId    AND
       t1.TransactionID = t2.TransactionID AND
       t2.rn = 1
ORDER BY t1.CustomerId

Output:

Demo here:

Rextester

Note that the demo is in SQL Server, because using Oracle on Rextester is scary, but the code should run also on Oracle without any issues.