payments |
---|
customer_id |
payment_id |
payment_amount |
payment_date |
If we want to see the payment_id of the highest amount of payment for each customer_id then we need to do:
select distinct customer_id,
first_value(payment_id) over (partition by customer_id order by payment_amount desc) as highest_payment_id
from payments
Note that you don't need to specify a group by
clause for the overall query in this case.