I have a table as follows :
Year (numeric)
Factor (text)
Test_1 (numeric)
Test_2 (numeric)
Test_3 (numeric)
Test_4 (numeric)
unique index(year,factor) (i.e. each factor only appears once per year)
What I need to achieve is an ordered ranking of the factors for each test, e.g. let's say I've got a factor called "Time". I need to count and order by the number of times the factor has ranked first,second,third etc. for a given test.
So if factor "time" in year 1 it came first in an order desc, and year 2 & 3 it came second in an order desc, the totals for "Time" would be first once and second twice.
I've been staring at my screen too long today and am messing up my ranks with my partitions with my order by's .... I'm just in an SQL syntax mess....so hopefully maybe somebody here has solved a similar conundrum before.
For extra kudos points, you might also wish to show me how the solution to the above can be done in conjunction with normalised zScoring of the tests (i.e. select Test_1-avg(Test_1)/stddev(Test_1) as Test_1Norm). ;-)
Thanks !
Tim