RE: Can you do this in SQL

["Alexei Pytel" <pytel@xxxxxxxxxx>]

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
end
close cursor C1


Alexei Pytel,
    speaking for myself