Dudu Markovitz Dudu Markovitz - 1 month ago 5
SQL Question

How to generate row numbers ordered by non-unique values?

I'm working with SQLLite and I have a table with integer column i, where i is not unique.

create table t (i int not null);


I need a query that returns the same results as the following query when executed on other databases such as Hive, Teradata, Oracle, SQL Server, PostgreSQL etc. -

select t.i
,row_number() over (order by t.i) as rn

from t
;


Data Sample



insert into t (i) values (1),(2),(3),(3),(4),(5),(5),(5),(6),(7);


Requested result



I RN
---------- ----------
1 1
2 2
3 3
3 4
4 5
5 6
5 7
5 8
6 9
7 10

Answer

SQLLite

with        i_occ (i,occ) as
            (
                select      i, count (*)              
                from        t                
                group by    i
            )

           ,seq_n (n) as 
            (
                select      1

                union all

                select      n+1 
                from        seq_n 
                where       n < (select max (occ) from i_occ)
            )

           ,i_accum_occ (i,occ,accum_occ) as
            (
                select      i, occ, (select sum (o2.occ) from i_occ o2 where o2.i < o.i)              
                from        i_occ o 
            )


select      o.i ,coalesce (o.accum_occ,0) + s.n as rn  

from                    i_accum_occ o    
            join        seq_n       s                
            on          s.n <= o.occ   

order by    rn            
;