Keep Column which is not in aggregate function in group by statement

Question

I would like to modify an existing select so it outputs the row with the MAX(amount) per customer and all of its values. At the moment the result is like this.

   AMOUNT CUSTOMERID     ITEMID USERNAME                        USERID SUMMARYDAY

    60    198507        205 luk                                   12 03.10.18
   300    198526        207 max                                   12 03.10.18
 20000    198507        126 luk                                   12 03.10.18
  6000    198526        158 max                                   12 03.10.18
  1200    198526        206 max                                   12 03.10.18

But I want this:

   AMOUNT CUSTOMERID     ITEMID USERNAME                        USERID SUMMARYDAY

  20000    198507        126 luk                                   12 03.10.18
  6000     198526        158 max                                   12 03.10.18

The query at the moment:

SELECT max(totalamount) as amount, cg.customerId, g.itemid,
       (select c.nickname from customer c where c.customerId=cg.customerid) as nickname,
       12 as clientId, sysdate as summaryDate
FROM ItemBuy cg,
     ItemToSell gf,
     Item g
WHERE cg.itemSellId = gf.itemSellId and gf.itemId = g.itemId
  and cg.type = 0 and cg.shopId = 12
  and cg.starttime >= sysdate-100 and cg.starttime < sysdate+100
group by cg.customerId
having max(totalamount) > 0

I anonymized the query a bit but I my main question is:

How can I keep specific colums with an group by statement and tell sql to just keep it after group by and max () have "choosen" a row.

Thank you so much in advance!

Answer 1

I would suggest this straight forward way and translate your description "outputs the row with the MAX(amount) per customer and all of its values" to SQL:

select * from a_table t
where (t.customerid,t.amount) in (
   select customerid,max(amount) from a_table group by customerid);

Let me know if you need more input.