[thelist] ranking entries in a database

rudy r937 at interlog.com
Tue May 14 21:44:01 CDT 2002


> Now I could store the position off the timesheet and be
> done with it, but I'd rather not since:
>
> a) I'm sure I read that its bad practice to store values
> that could be calculated

hi alastair

this is true only up to a point

redundant data is stored all the time, for efficiency

for example, if you look at the front page of evolt, each of the articles
shows you how many comments have been made on the article -- that comment
count is stored redundantly, so as to avoid the join from the article table
to the comment table (yes, this means the article record has to be updated
when a comment is added)

> b) The timesheets have a nasty habit of being wrong
> (meaning lots of manual correction)

you need to balance the effort to correct a lot of records by hand against
the complexity of the maintenance procedures that you need to code in order
to re-calculate positions after a timesheet has been corrected

> So my question is, how do I calculate the rank of each rider??
> Is this an SQL problem or an ASP one??

since you suggested that the positions need to be available for other
purposes, it more or less requires that the positions be stored, so it's an
SQL problem

let's say the race results are as follows

mary  2.24
bill  2.30
fred  2.35
jane  2.35
john  3.01

i've listed them in order of finish, but what you want is a query that
generates a ranking

  select t1.ridername, t1.ridertime
    , (select count(*) from raceresults) - count(*) + 1 as position
    from raceresults as t1
           , raceresults as t2
  where t1.ridertime <= t2.ridertime
 group by t1.ridername, t1.ridertime
  order by 3

i won't go into the details of how this works unless you really care

anyhow, suffice to say that the results properly account for ties

what you would do with this is save the results into another table, using
either

  insert into table2 select ...

or (in sql/server or access)

  select .... into table2  from ...

now whenever a timesheet entry has to change, you simply re-run this query

the reason you want to store the finish positions is because you do not
want to be trying to join the above query with other tables


rudy




More information about the thelist mailing list