Just as an exersize - I can think of two ways of doing this in SQL:

1) in a single statement:

      update cus tu set rank =
      ( select count(*) from cus ts
         where tu.yr = ts.yr and tu.money <= ts.money )

which is not the best performer

2) if we are not fixed on doing it all in one statement, it can be done
quite easy and efficiently.
Without much detail and with a loose syntax:

declare cursor C1 for select yr, money, rank
                                          from cus order by yr, money
                                          for update of rank
open cursor C1
do while not EOF
  fetch C1 into :yr, :money, :rank
<increment rank, taking account year changes>
  update set rank = :rank where current of C1
close cursor C1

Alexei Pytel,
    speaking for myself

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.