We help IT Professionals succeed at work.

Select only the maximum amount for each customer from a list MS 2010

frimy
frimy asked
on
73 Views
Last Modified: 2018-11-26
I have list from all customers the daily open balance by date (I'm getting it from the Running Sum total).
I need to get only the largest Open Balance for any date for each customer.

I'm using created the following query.
It only works with little records.
But with a lot records I get an error "At most one record can be returned by this subquery"
Thanks in advance

MaxAmt: (SELECT top 1 [RunningSum ] FROM [qCustBal AddRunAmt] WHERE CustomerID=[Customer].[CustomerID] ORDER BY RunningSum Desc)
Comment
Watch Question

Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Try this for tbl (CustID, fld) to get maximum value of fld for each CustID.

SELECT a.CustID, (SELECT MAX(b.fld)  FROM tbl AS b WHERE b.CustID = a.CustID)
FROM tbl AS a
GROUP BY a.CustID

Open in new window

Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Provide some sample data and expected result.

Author

Commented:
Hamed,

it works very good.
but it's running very slow.

I have 5,630 records (test only)
to add a running total it takes about 3 seconds (from Dale)
But to the get the maximum value for each customer (the greatest amount), it takes about 9 minutes to run.
Is it possible to make it faster?
Thanks
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
If you have other needs, the ordinary Group By query can be part of the FROM clause of another (higher level) query, joined on the custid field.
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
I go with aikimark's comment, for this question.

Author

Commented:
aikimark, it works beautiful, it's funny I didn't think of it because I use it all the time.
maybe because I was focusing on the Add Running total query.
thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.