On Thu, 25 Sep 2014 18:40:23 +0200
RSmith <rsm...@rsweb.co.za> wrote:
> It's much harder to make a mathematical positioning result than
> simply working out if the current is better or worse than any
> existing position.
So you say. But someone is doing *something* to determine who comes
before whom. Even synchronized swimming gets a score.
> the time of finishing does not determine position alone, there are
> bonuses and penalties which doesn't stack up to integer values, but
> is measurable.
Measurable means "is a number", right? So we have math, and IEEE
floating point. Yay! We can compute the answer! ;-)
> so there is an action of finding a best-fit position in the list for
> the newest finisher and "insert" him/her there.
There are two possibilities: The "corrected" time -- the ranking
value -- either
a) does, or
b) does not
depend on prior finishers in the same race. I've never heard of a
type (a) system, so I assume yours is type (b). That means the computed
result is a function F with the form
corrected time = F( time, parameter, [parameter...] )
where the parameters are constants or are computable from pre-start
attributes of the contestant or contestants.
Record the actual time in the database with the race. Maintain tables
of contestant attributes and parameters required for F(). Write a view
to compute the corrected time. SELECT, ORDER BY, and go home! :-)
I can appreciate that you don't want to do the computations in SQL,
especially if the requisite math functions aren't available. You then
have the options of computing it first and storing corrected time, or of
writing the C code as a user-defined SQLite function. Either one is
more robust and less opaque than your divide-and-insert strategy, or a
recursive CTE.
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
