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 to get the expected results.I will follow the steps as suggested from next time while posting my question. Thanks all for your replies.

Thanks