Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: Alternative for rank() operator

    Hi,

    I am using rank() over(partition by ) operator to find rank in a query.

    But in performance side, partition by is ver slow and query is executing a very long time.

    can you please suggest an effective alternative to this partition by?

    Thanks.

  2. #2
    Join Date
    Jan 2010
    Posts
    25
    Let say you need the output like this:

    EmpId RNK
    101 1
    101 2
    101 3
    102 1
    102 2
    103 1


    step 1:
    Polulate and identity column

    EmpId Identity
    101 1
    101 2
    101 3
    102 4
    102 5
    103 6


    Step 2: take the min of the identity for each of the group of EMpId


    EmpId Identity min(identity)
    101 1 1
    101 2 1
    101 3 1
    102 4 4
    102 5 4
    103 6 6


    Step 3: Subtract the identity field from min(identity) and add 1


    EmpId Identity min(identity) rank
    101 1 1 1
    101 2 1 2
    101 3 1 3
    102 4 4 1
    102 5 4 2
    103 6 6 1

Posting Permissions

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