Oracle - select top transaction for each user_id

Question

I have a select statement that gets user_id and a list of transactions for the day such as this:

select user_id, sale_amount, date, product from transactions

I want to be able to select each user_id (there are many) along with their top sale_amount, date and product. If there is a tie, I want it to just select one. How is this possible? Rownum or rank seem to be close but not quite there?

Answer 1

I m not ifo computer but this should work. Let me know select * from (select user_id, sale_amount, date, product,row_number() over (partition by user_id order by sales_amount desc) as maxsale from transactions) l where maxsale=1