I am using MySQL 4.0.22 and have a question about an efficient way to calculate
a running balance as part of the select. The natural way to do this would seem
(to me) to be something like:

  set @balance:=0;
  select payid,Custid,Paid,@balance:[EMAIL PROTECTED] from payments;

This does indeed seem to work. However the way I read the explanation on user
variables I would not expect this to work. I found a solution for this from the
MySQL Cookbook using a self join which for the above would be:

  select t1.payid,t1.Custid,t1.user,t1.Paid,sum(t2.Paid)
    from payments as t1, payments as t2
    where t1.payid>=t2.payid group by t1.payid;

This also works but as I am sure most of you know will not scale to even a
few thousand records.

Will using the user variable work? If not where might I find some
alternate techniques?

_____
Douglas Denault
http://www.safeport.com
[EMAIL PROTECTED]
Voice: 301-469-8766
  Fax: 301-469-0601

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to