Skip to Main Content
  • Questions
  • Generating rownumbers without rownum,row_number(),sum() over and rowid in oracle

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Guru.

Asked: October 24, 2016 - 5:14 am UTC

Last updated: October 24, 2016 - 12:54 pm UTC

Version: 11g R2

Viewed 1000+ times

You Asked

Recently one of my friend faced a question in one of the interview to generate rownumbers without using rownum or Row_number() function.
He had suggested to do to running sum of 1's but that was not the right solution as the table contained duplicate values.He suggested not to use rowid column aslo.
Person asked him to generate numbers for the below table in ANSI SQL format.Is there any way we can do this?

Ex : Table_a

col_a
a
b
c
d
a
b

and Connor said...

select level from dual connect by level <= 100

will generate rows numbered 1 .. 100.

I'm not sure what you mean by "generate numbers for the table below"

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Guru M, October 24, 2016 - 7:00 am UTC

Rownumber generation should be done using ansi sql for non unique rows. Connect by level not supposed to used.

Rownum generation without using rownum,ROw_number(),sum() over,rowid and connect by level

Guru M, October 24, 2016 - 7:11 am UTC

They wanted rownum generation using ansi sql ..not any oracle specific functions.
Chris Saxon
October 24, 2016 - 12:54 pm UTC

Row_number and sum are part of ANSI SQL!

You could use common table expressions/recursive subquery factoring:

with rws (r) as (
  select 1 r from dual
  union all
  select r+1 r from rws
  where  r < 10
) 
  select * from rws;

R   
1   
2   
3   
4   
5   
6   
7   
8   
9   
10


Or other ways shown at:

https://blogs.oracle.com/sql/entry/fizzbuzz

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.