Thank you both again.

Here's what I used before posting the question here.

select (

select count(*)from products

as temp

where convert(int,(right(temp.product_id,6)))<

convert(int,(right(products.product_id,6)))

)+1 as no, product_id

from products

I was looking for something else simpler than that. The subqueries will work if we have a field can be compared or we will end up with creating a temp table like you said.