Results 1 to 5 of 5

Thread: Rank Equation

  1. #1
    Join Date
    May 2006
    Posts
    65

    Unanswered: Rank Equation

    Hi all,

    until recently ive been using a rank equation to calculate rank,

    essentially doing a select statement and selecting this as the rank field, where query 2 is the same as query 1:

    ((select count(*) from (query1) where (query1).value < (query2).value) +1)) as Rank

    problem is that this is now running like a dog (takes 10 secs) and i'd like to try and do this another way- 2005 has a rank function, how can i do this in 2000?

    here is the full statement :

    SELECT StudentId, GCSE_Score, LTRIM(STR
    ((SELECT COUNT(*)
    FROM dbo.[Score2004-05]
    WHERE GCSE_score < s.GCSE_Score) + 1)) AS GCSE_Rank, SetId
    FROM dbo.[Score2004-05] S
    WHERE (SetId = '2004/2005')

    greg

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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:
    Code:
    SELECT	StudentId,
    	GCSE_Score,
    	count(*) AS GCSE_Rank,
    	SetId
    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,
    	GCSE_Score,
    	SetId
    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2006
    Posts
    65
    hi,

    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

    greg

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't have to create a table. Just a temporary table (you know, with the "#" designation?)
    Without indexes, the temptable/sproc route is your best bet for performance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2006
    Posts
    65
    you know, i didnt know that- excellent!! im dead happy with that...im off to experiment with temp tables.

    greg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •