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(25), primary key (account_num) )
The numbers table has 1mil rows.
2 Answers
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
answered May 29, 2011 at 14:46
Denis de Bernardy
79.1k14 gold badges138 silver badges158 bronze badges
1 Comment
mwok
Is it possible using a numbers table though?
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
row_number()not good for you? Not available?