Max of sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max of sum

Author

Topic

zoe2003
Starting Member

17 Posts

Posted - 2015-04-20 : 05:53:30

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

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

Thank you in advance.
Z.

Maithil
Starting Member

29 Posts

Posted - 2015-04-20 : 06:37:35

Hi,

This SQL SERVER Blog

Ask same in Oracle Blog

Maithil
Starting Member

29 Posts

Posted - 2015-04-20 : 06:40:28

SELECT
*
FROM YourTable where Credit_card =(select Credit_card from Your Table where Amount=(select MAX(Amount) from your Table))

zoe2003
Starting Member

17 Posts

Posted - 2015-04-20 : 07:57:38

quote:
Originally posted by Maithil

SELECT
*
FROM YourTable where Credit_card =(select Credit_card from Your Table where Amount=(select MAX(Amount) from your Table))

@Maithil, Thanks, but what if I have more clientIDs, like :
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 this result:
client creditCard
1 B
2 D

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-20 : 09:29:25

[code]
select client, Credit_card
from your_table
join
(
select client as max_client, MAX(amount) max_amount from your_table group by client
) _
on client = max_client and amount = max_amount
order by client
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources