Simulate row number using numbers table

How would I simulate row number for a table using a numbers table WITHOUT using ROW_NUMBER() function.

sample table: create table accounts ( account_num VARCHAR(开发者_如何学C25), primary key (account_num) )

The numbers table has 1mil rows.

In case you're meaning, when it's not available (aka MySQL), try something like this:

select @rownum := @rownum + 1 rownum,
       t.*
from (select * from table t order by col) t,
     (select @rownum := 0) r

It'll yield the same as:

select row_number() over (order by col)
from table
order by col

A Numbers table does not help you here because you have no means to associate a value in your table with a number in the Numbers table. However, if you are asking whether it is possible to create a sequence without using ROW_NUMBER() or a variable, you can do it like so:

Select A1.Account_Num, Count( A2.Account_Num ) + 1 As Num
From Accounts As A1
    Left Join Accounts As A2
        On A2.Account_Num < A1.Account_Num
Group By A1.Account_Num