Link to home
Start Free TrialLog in
Avatar of larryluck
larryluck

asked on

Group-wise Maximum (i think)

Hi,

I am trying to query a large table with the following columns:
|Member (varchar) | Month (date) | Amount1 (int) | Amount2 (int)|

For each member, I want to find the month where Amount1+Amount2 is highest (max).

Output would be:
|Member | Month of Max | max(amount1+amount2)|

I haven't been able to find the right answer online. I appreciate any help!

Larry
Avatar of oheil
oheil
Flag of Germany image

SELECT Member,Month,amount1,amount2 from LargeTable where (amount1+amount2) in 
(
  SELECT max(amount1+amount2)
  FROM LargeTable Group by Member;
)

Open in new window


If the maximum is in more than on month, all those would be selected.

Oli
Avatar of larryluck
larryluck

ASKER

The table in question is very very large (150M records), so I haven't gotten this query to execute in a reasonable amount of time.
As an alternative, I've tried to break the problem into a few smaller pieces - will check back in with results.

Thanks for the input
Larry
ASKER CERTIFIED SOLUTION
Avatar of oheil
oheil
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial