[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