Thanks for the reply.

I solved the problem myself.

I created three temp tables for each condition using the row over partition and then joined them with the main order table...