Thank you for your replies.

Have already tried assigning local variables method by GSquared but returned the following error:

"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

I prefer to use this method, but if no suggestions to resolve this error, will try the temp table method by SeanLange.