I'm looking for a SQL solution that will find combinations of low and high scores in a series of tests. Records might look like:
date,skill,test,score,person_id
10/01/02,1,5,12,keyesl
10/01/02,2,7,15,keyesl
07/01/03,1,5,14,keyesl
The test records are in order of their giving, but are not in sequcncial pairs.
In the example above, I want to retrieve the first and third records only, so that I can ultimately compare the two scores, (12, and 14) and compute the difference between the two. I know that the first and third records are the ones I want because:
The dates are different and the second date is later than the first
skill and test values match. (i.e. this is the same test).
There may be more than a single pair of tests for each person_id.
There will be many single tests that do not have a matching test.
There may be three or more tests which need to be dealt with as pairs.
TIA
-- Larry Keyes
Remember only You can prevent Gray Goo. Never release nanobot assembers without replication limiting code.