Max on Sum [message #636223] |
Mon, 20 April 2015 08:03 |
|
zoe2003
Messages: 2 Registered: April 2015
|
Junior Member |
|
|
Hi,
I have this data:
client creditCard amount
1 A 10
1 A 20
1 B 40
1 C 5
1 C 10
1 C 20
2 A 40
2 D 60
I need to write a query (Oracle) that will return the clientID and the creditCard type with the highest spending :
In this case:
client creditCard
1 B
2 D
Tnx in advance.
z
[mod-edit: code tags added by bb]
[Updated on: Fri, 08 May 2015 07:31] by Moderator Report message to a moderator
|
|
|
|
Re: Max on Sum [message #636226 is a reply to message #636223] |
Mon, 20 April 2015 08:20 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
zoe2003 wrote on Mon, 20 April 2015 18:33
I need to write a query (Oracle) that will return the clientID and the creditCard type with the highest spending
So what's the problem in using MAX? In a sub-query, find maximum amount grouped by client. Alternatively, you could also use analytic ROW_NUMBER(). What have you tried so far?
|
|
|
|
|
Re: Max on Sum [message #636892 is a reply to message #636890] |
Tue, 05 May 2015 03:50 |
gazzag
Messages: 1118 Registered: November 2010 Location: Bedwas, UK
|
Senior Member |
|
|
SQL> select client, credit_card, max(amount)
2 from t1
3 group by client;
select client, credit_card, max(amount)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
|
|
|
|
Re: Max on Sum [message #637046 is a reply to message #636893] |
Fri, 08 May 2015 05:23 |
|
saipavan.plsql
Messages: 17 Registered: February 2015 Location: chennai
|
Junior Member |
|
|
this will help you
select client, creditCard
from (select client,
creditCard,
dense_rank() over(partition by client order by amount desc) ranking
from (select client,
creditCard,
max(amount) amount
from prc_table_1
group by creditCard, client))
where ranking = 1;
Tnx,
Sai Pavan
[Updated on: Fri, 08 May 2015 05:54] Report message to a moderator
|
|
|
|
|
Re: Max on Sum [message #640232 is a reply to message #640230] |
Fri, 24 July 2015 04:57 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Nope - that works with OPs example data but won't in general.
What happens if you add this row:
Plus you do realise this thread is 2 months old. You'd like to hope the OP had solved their issue by now.
|
|
|
|
|
|
|
Re: Max on Sum [message #640434 is a reply to message #640422] |
Tue, 28 July 2015 08:54 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That query also doesn't work in general for the same reason nqtrung's attempt above doesn't work.
Add the extra row of data I suggested and see what you get.
|
|
|
|
|
Re: Max on Sum [message #640469 is a reply to message #640457] |
Wed, 29 July 2015 02:45 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
nqtrung wrote on Wed, 29 July 2015 03:32
SELECT CLIENT, CREDITCARD, AMOUNT FROM T1
WHERE (CLIENT, AMOUNT) IN (SELECT CLIENT, MAX(AMOUNT) FROM T1 GROUP BY CLIENT);
Hope it's right
Oh good grief.
That's just prabhu.MS's attempt with the distinct removed. Of course it's not right, for the same reason I've already pointed out.
You need the client/credit card where the sum(amount) for that client/credit card is the greatest of all the client/credit card sums for each client.
|
|
|