Finding and saving the position of a record in a sorted table

[sunshinemike]

Hi

 

I have a problem that must be easy to sort out.

 

I have a simple database storing sports teams results.

 

At the end of each month I sort the table and display the teams in order of 'points scored'

 

I want to be able to show if a team has gone up or down in the league table

 

 

eg

 

Team 1    101 points    -  (were in first place last month)

Team 2      99 points    +1 (were in 3rd place last month)

Team 3      85 Points    -1 (were in 2nd place last month)

Team 4      80 Points    +3 (were in 7th place last month)

 

 

I have added two new fields to my table called 'position' and 'last_position'

 

I wish to sort my table and replace the contents of the field 'position' with the current league placing so I can calculate the changes in positions from the previous month.

 

So , when I have a sorted table how can I record (in a field) the position of the record in the sorted table?

 

In days gone by when I used Dbase 3 I could simply use the function recno() (the record number in the sorted table). Is there a similar function in MySql ????

 

Many thanks

 

Mike

 

 

 

[fenway]

You can use user variables to keep counters.