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!
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);
さらに入力が必要な場合はお知らせください。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加