[thelist] ranking entries in a database

[Michiel Trimpe]

Hello Alastair,

You could make it an SQL problem as well as an ASP problem. The pure
SQL problem that I came up with quickly seems quite cumbersome
though, and I'm not sure it can be done by anything except Oracle.

The best way I could think of is doing an ordered select, such as :
SELECT ID, Category, (expression) AS CalculatedScore FROM Players
ORDER BY Category, CalculatedScore ;

And now in ASP do a while loop trough the list such as (pseude code)

WHILE Row = NextRow() :
      If ( Row.Category != OldCategory ) Then Counter = 0
      Counter = Counter + 1
      UPDATE Players SET Ranking = Counter WHERE ID = Row.ID
      OldCategory = Row.Category
END WHILE

I know this isn't the best way, but it'll work.
If you dive into it there might be an all SQL access equivalent,
but I just reinstalled windows so I can't try it out.

GL,
   Michiel




--
Best regards,
 Michiel                            mailto:M.B.Trimpe at student.tue.nl