How to do sql grouping correctly?

Question

Oracle

I have a table from which I load the fields and name them according to a new one, I do the sum for one field, but in this case group by does not work, because an error comes out - it does not know how to group the remaining fields (not a group by expression) , I can’t include these fields in group by, since they are not yet in the table.

Having tried partition by, everything is considered normal, but I just need to merge strings as in a group buy

Is there any way to remove extra lines in the partition or set up a group buy with new columns (creating an additional table is not an option)?

Answer 1

Number with Rank or dense_rank with the same partition with rownum sorting and cut off the excess with a wrapper:
select * from (...) where rnk = 1
PS: It's better to do without window functions if there is a more classical solution. The windows force to bypass the selection again and worsen the query execution plan.