That is going to be horribly messy, since you'll need to find the single (staffid, effectivefrom)
pair that's the newest prior to the dateParameter
.
What I'd do is add another field effectiveTo
to go with effectiveFrom
, and then when you add a new target for a staff member, before you add it check and see if there are any current targets (effectiveTo IS NULL
) and if there are then set their end date to the new target start date (optionally: throw an error if there's more than one current target).
So, the table turns out looking like:
staffID | target | effectiveFrom | effectiveTo
--------|--------|---------------|-------------
1 | 6.0 | 2012-01-01 | 2012-03-01
2 | 6.0 | 2012-01-01 | NULL
3 | 6.0 | 2012-01-01 | NULL
1 | 7.0 | 2012-03-01 | NULL
That makes your search query look like this:
SELECT othertable.*, targets.target
FROM othertable
INNER JOIN
targets ON othertable.staffid = targets.staffid
WHERE dateParameter > effectiveFrom AND (dateParameter <= effectiveTo OR effectiveTo IS NULL)
The potential downside of that is data inconsistency (i.e. staff members having overlapping targets) - you can fairly easily write a script to check that though. I'd recommend making an addTarget(staffID, target, fromDate)
stored procedure and wrapping all the checking/updating logic in that, also.
(A couple of style notes - I rewrote your query slightly to seperate the join condition from the where clause. Also, I know it's only an example, but SELECT table.*
is a bad habit to get into - you land up transferring far more data than you need.)
Assuming that you are prepared to compromise on the absolute correctness of the answer in order to obtain practical performance, you could do the following:
- Create an index on
score
(assuming descending, but either way is workable)
- Create a maintenance job to rebuild this index periodically (how often depends on your willingness to have your rank results be out of date)
I take it that you are OK with these assumptions, since you have noted an index on score
in your question.
Then your user's global ranking for their scores would be something like this:
select
S.*
, count (R.id from highscores R
where R.score > S.score) as rank
from highscores S
where S.alias = 'somealias'
Using correlated subqueries is not a recipe for blinding speed, but the advantage of this is that you don't have to rank everything, you just have to count entries in an index relative to a user's particular scores. Of course, only your query optimizer will know for sure.
Best Answer
Timestamping each row doesn't seem to make a lot of sense to me. I would just keep a log of the events that are interesting. Such a log could be reusable for other events too. E.g. (sorry this is SQL Server dialect, you may have to adjust slightly):
Whenever you reload the table, also call a stored procedure that does this:
If you need to know the date of the last refresh, you can say:
If all you ever care about is the last time, then before the insert above, you could just: