Well, I'd dump the LTRIM/STR first, 'cause I don't know why you would want to convert a perfectly good number to a useless string.
Then, you can give this alternate method a shot, though it generally runs the same speed as the method you have tried:
count(*) AS GCSE_Rank,
FROM dbo.[Score2004-05] S
inner join dbo.[Score2004-05] T on s.GCSE_score >= t.GCSE_Score
WHERE SetId = '2004/2005'
group by StudentId,
I assume that GCSE_score is indexed?
Another method that can be used in a stored procedure is to load all your data into a temporary table with an IDENTITY value in sorted order. Then select from the temporary table as output.
This is usually pretty fast.
If it's not practically useful, then it's practically useless.
ah the string conversion is because im combining the value into a string in the next query but will do the convesrion at that stage rather at the stage when it's trying to work out the rank.
GCSE_SCORE isnt indexed because its a view which combines data from my non-indexed database (yes the db i ref is not indexed! not my doing, long story!)- other than that i would have tried the tored procedure method but didnt want to have to create a table, i wanted the ranks to automatically update when ever a value was changed in the main data.
so will try that other method, thanks for showing me this