If I have a set of transactions and want to pick out the full transaction of the largest or latest transaction, how do I identify and extract that transaction. e.g. given a table like
| ID | ItemID | Qty | Value | TranDate | CustID |
|——|——–|—–|———|———–|———-|
|11254 | a123 | 10 | 123.60 |10/01/2021 | FH12AH |
|11236 | a124 | 1 | 1123.60 |15/01/2021 | FH12AH |
|1123 | a129 | 9 | 23.10 |15/01/2021 | FH12AH |
|11237 | a125 | 5 | 213.10 |15/01/2021 | FH12AH |
|2134 | a123 | 3 | 37.08 |15/01/2021 | QB876G |
|3412 | b987 | 31 | 123.60 |23/01/2021 | QB876G |
|4321 | jp34 | 5 | 123.60 |30/01/2021 | JH8765 |
|4322 | a123 | 51 | 1123.60 |02/01/2021 | TT6548 |

how would I extract the entire transaction for

1. the highest value transaction for each customer
2. the highest value transaction for each product

I can write a query to extract the date of the highest value of transaction per customer like this:

Select CustID, max(value) as highest_value
from Table
Group By CustID

But to extract the entire transaction, I need to JOIN that to the original table as a subquery, i.e.

select t.* from Table t
Join (Select CustID, max(value) as highest_value
from Table
Group By CustID) sq
on t.CustID = sq.CustID 
and t.value = sq.highest_value

Is there a simpler construct to do the same job?

TIA