Find largest in multiple groups

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

SQL Server 2008 Forums

Transact-SQL (2008)

Find largest in multiple groups

Author

Topic

ewomack
Starting Member

33 Posts

Posted - 2010-09-09 : 09:42:46

Hello,

I'm currently struggling with this one. I have a bunch of IDs (each representing 1 person) that each have 1 or more accounts that they have dollar amounts for. I need to find the largest amount for each id given a particular fund. A sample will probably help demonstrate:

ID account amount
-- ------- ------
02 1234567 $100.00
02 2345678 $50.00
02 3456789 $2000.00
02 4567890 $75.00
15 1234567 $1000.00
15 2345678 $500.00
15 3456789 $25.00
15 4567890 $700.00

I have a collection of groupings like the above chart and need to find all id#s who have their largest amount in a particular account. So, for instance, I need to list all ids who have their largest amount as account "3456789." Running this query against the data above would return ID 02 (since account 3456789 has the largest amount of all other accounts), but not ID 15.

My instinct tells me variables and looping, so that's where I'm starting. Has anyone else had to SQL this issue?

Thank you!

Ed Womack
www.getmilked.com

ewomack
Starting Member

33 Posts

Posted - 2010-09-09 : 09:57:54

I thought of one solution involving temp tables, but I'd rather avoid it if I can. Insert the following query into a table

insert into stinking_temp_table(ID, account, amount)
select ID, account, max(amount)
from table
group by ID, account
order by ID

Then query this table
select ID, account, amount
from stinking_temp_table
where account in ('3456789')

This should at least give me everyone with a max amount in that account.

Ed Womack
www.getmilked.com

ewomack
Starting Member

33 Posts

Posted - 2010-09-09 : 10:02:25

Nope, this just moves the problem. I'll stop thinking online...

Ed Womack
www.getmilked.com

parody
Posting Yak Master

111 Posts

Posted - 2010-09-09 : 11:44:33

are you always specifying the account?

if so:

select top 1 ID
from table
where account = ?
order by amount desc

parody
Posting Yak Master

111 Posts

Posted - 2010-09-09 : 11:47:57

this raises some Q's on what you would do if 2 ID's had the same amount. I'd probably use a ranking function and take where rank = 1 top get all of them if required

i.e:

with cte as
(select
ID,
rank() over (partition by amount order by amount desc) as rank
from table
where account = ?)

select * from cte where rank = 1

all on the fly might be some syntax amiss!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources