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.

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