2

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!

MT0
  • 143,790
  • 11
  • 59
  • 117
user3241778
  • 294
  • 2
  • 4
  • 20
  • So you want only one row per user? Which one? – jarlh Oct 03 '18 at 07:20
  • 1
    (Unrelated) Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Oct 03 '18 at 07:21
  • Your query and current/expected output do not match. This could make it difficult for someone to help you. Please clean up your question. – Tim Biegeleisen Oct 03 '18 at 07:21
  • @jarlh yes I want one row per user and it should be the row with the highest amount per user – user3241778 Oct 03 '18 at 07:23
  • @TimBiegeleisen I think should maybe just ask the question without a query next time. My main question is. How can I keep the not-aggregated rows of my statment which includes a group by (over the user column) and a max (over the amount column). Thanks – user3241778 Oct 03 '18 at 07:31
  • I think you should post data and a query which actually match. – Tim Biegeleisen Oct 03 '18 at 07:46
  • 1
    Use analytic functions; either use the [`MAX(..) OVER(..)`](https://stackoverflow.com/a/121450/1509264) analytic function (i.e. `SELECT * FROM ( SELECT ..., MAX( totalamount) OVER ( PARTITION BY customerid ) AS mx FROM ... ) WHERE totalamount = mx`) or [`RANK() OVER ( ... ORDER BY ... )`](https://stackoverflow.com/a/121693/1509264) analytic function (i.e. `SELECT * FROM ( SELECT ..., RANK() OVER ( PARTITION BY customerid ORDER BY totalamount DESC ) AS rnk FROM ... ) WHERE rnk = 1`) – MT0 Oct 03 '18 at 09:04

1 Answers1

3

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.

Peter
  • 932
  • 5
  • 19